|
View previous topic :: View next topic
|
| Author |
Message |
itjagadesh
New User
Joined: 05 Dec 2006
Posts: 89
Location: chennai
|
|
|
|
Hi Team,
i have following requirement below,
Table1 contains invoiceid and date.
Table2 contains invoiceid and status.
Requirement --
Requirement is to match invoiceid in TABLE1 with TABLE2 and STATUS CODE =' A' AND STATUS CODE NOT EQUAL TO 'C ' OR 'D'
TABLE1
INVOICE ID DATE
1 2000
2 2001
4 2002
TABLE2
INVOICE ID STATUSCODE
1 A
1 B
2 A
2 B
2 C
3 A
3 B
3 D
4 A
4 D
OUTPUT
INVOICE ID DATE
1 2000
In above example,for invoice id '2' and '4' , status code = 'C' or 'D'.So it should not come in output.
can anyone please give me the query
Thanks
Jagadesh |
|
| Back to top |
|
 |
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
itjagadesh,
This might be of some help to you(untested).
| Code: |
SELECT T1.INVOICE-ID,T1.DATE
FROM TABLE1 T1
,TABLE2 T2
WHERE T1.INVOICE-ID = T2.INVOICE-ID
AND NOT EXISTS
(SELECT 1
FROM TABLE2 T3
WHERE T3.INVOICE-ID = T1.INVOICE-ID
AND T3.STATUSCODE IN ('C','D'))
|
|
|
| Back to top |
|
|
itjagadesh
New User
Joined: 05 Dec 2006
Posts: 89
Location: chennai
|
|
|
|
Hi Arun,
Thanks for your quick response.
But my requirement is to select STATUS CODE = 'A' also .
Requirement --
Requirement is to match invoiceid in TABLE1 with TABLE2 and STATUS CODE =' A' AND STATUS CODE NOT EQUAL TO 'C ' OR 'D'
Thanks
Jagadesh |
|
| Back to top |
|
|
abhishekmdwivedi
New User
.jpg)
Joined: 22 Aug 2006
Posts: 95
Location: india
|
|
|
|
Hi,
I think below code will result in the desired output : ( Ustested )
| Code: |
Select a.invoice_id,a.invoice_dt from table1 a, table2 b
where a.invoice_id = b.invoice_id
and b.invoice_st = 'A'
and not exists (Select 1 from table2 c
where a.invoice_id = c.invoice_id
and c.invoice_st in ('C','D'));
|
|
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Quote: |
Thanks for your quick response.
But my requirement is to select STATUS CODE = 'A' also . |
I missed it. But you can modify it to add that condition.
| Code: |
SELECT T1.INVOICE-ID,T1.DATE
FROM TABLE1 T1
,TABLE2 T2
WHERE T1.INVOICE-ID = T2.INVOICE-ID
AND T2.STATUSCODE = 'A'
AND NOT EXISTS
(SELECT 1
FROM TABLE2 T3
WHERE T3.INVOICE-ID = T1.INVOICE-ID
AND T3.STATUSCODE IN ('C','D')) |
|
|
| Back to top |
|
|
jitu852
New User
Joined: 30 Apr 2007
Posts: 27
Location: Hyderabad
|
|
|
|
Hi,
if iam right no need of sub query it is simply
select columns,
from table1 t1,table2 t2
where t1.column1 =t2.column1
and t2.status = 'a' |
|
| Back to top |
|
|
Rajesh Midde99
New User
.jpg)
Joined: 04 Dec 2008
Posts: 11
Location: Bangalore,India
|
|
|
|
Hi
It will be help ful to others if the topic initiator responds if he is satisfied with the response and let every body know if the query is working or not ?? Please close the query with a response.
Regards
Rajesh |
|
| Back to top |
|
|
itjagadesh
New User
Joined: 05 Dec 2006
Posts: 89
Location: chennai
|
|
|
|
Hi All,
Thanks for your help.
Arun Query working fine.
Rajesh Sorry i was out of office.Not able to reply.
Thanks a lot to all.
Jagadesh |
|
| Back to top |
|
|
|
|
