View previous topic :: View next topic
|
Author |
Message |
rajiv rengasamy
New User
Joined: 24 Sep 2008 Posts: 26 Location: Chennai
|
|
|
|
Hi ,
Have a table of the below layout and for every plant there is a JAG and a TRD row.I need to find that site that has only JAG row and no TRD row.
Plant Div Code Colum1 Column2
AAAA JAG 123 456
AAAA TRD 345 789
BBBB JAG 567 1ab
BBBB TRD 890 ab4
CCCC JAG 12a xyz
CCCC TRD 34b 458
DDDD JAG 567b 406
So i my qurey should get the row DDDD (Site value).
I did this by using to qureys in a program
1))Get the JAG site from table
2)Check if there is already a TRD equivalent for this site in table for that site.
I am curious if this can be achieved in a single query please let know if any of you got some idea.
Thank you. |
|
Back to top |
|
|
saiprasadh
Active User
Joined: 20 Sep 2006 Posts: 154 Location: US
|
|
|
|
Rajiv,
Below query will satisfy your requirement.
Code: |
select * from
table1 a
where
div_code = 'JAG'
and not exists
(
select 1 from
table1 b
where
a.plant=b.plant
and b.div_code='TRD')
|
|
|
Back to top |
|
|
sureshpathi10
Active User
Joined: 03 May 2010 Posts: 154 Location: Kuala Lumpur
|
|
|
|
Rajiv,
You may try this as well.
Code: |
SELECT *
FROM TABLE A, TABLE B
WHERE A.PLANT = B.PLANT AND A.DIV = 'JAG' AND B.DIV <> 'TRD' |
|
|
Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
Hi Suresh,
I doubt your query will work as expected.
Your query will return the plant AAAA as well which is not required as per expected output?
Correct me if I am wrong.
Regards,
Chandan |
|
Back to top |
|
|
sureshpathi10
Active User
Joined: 03 May 2010 Posts: 154 Location: Kuala Lumpur
|
|
|
|
Yes Chandan. Thanks for find out. It will fetch all 'JAG' records.... |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Before posting a "solution" you need to make sure it does what is requested - not just something you chose to do. |
|
Back to top |
|
|
|