|
View previous topic :: View next topic
|
| Author |
Message |
sthirumalai
New User
.jpg)
Joined: 14 Aug 2007
Posts: 14
Location: Chennai
|
|
|
|
| Code: |
TABLE1
SNO
1
2
TABLE2:
SNO DATE_FIELD_01 DATE_FIELD_02
1 2013-01-01 2013-01-01
1 2013-01-01 2013-01-01
1 2013-01-01 2013-01-01
1 2013-01-01 NULL
2 2013-01-01 2013-01-01
2 2013-01-01 2013-01-01
2 2013-01-01 2013-01-01
2 2013-01-01 2013-01-01
|
I need a query which satisfies the following condition.
If there is a matching SNO from TABLE1 in TABLE 2 and no DATE_FIELD_02 value in table2 is NOT NULL then the
query should pick that SNO alone
In the above example the query should return the SNO as 2
Your help is appreciated. |
|
| Back to top |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Do you mean:
If there is a matching SNO from TABLE1 in TABLE 2 and no DATE_FIELD_02 table2 is NULL then the query should pick that SNO alone.
Otherwise, how would 2 be selected? |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
sthirumalai,
How about writing a correlated sub query which selects each SNO from TABLE1 with a NOT EXISTS check in TABLE2 for DATE_FIELD_02 IS NULL for the selected SNO. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Thats a double -ve Bill.  |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Thanks Arun. It's funny, I read it several times earlier, and couldn't get it to make any sense to me. Now, reading it again, I can't see what I found so unclear. Perhaps I was still asleep :-) |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Hi Bill,
I meant you were right, now I see that I made the same mistake as the OPs, edited my NOT NULL to NULL now. 
Have a good one! |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
I must still be asleep. I've changed the subject from all CAPS, just for something simple to do... topics then fit better on the page.
If TS would confirm, I'd change the original and have done with it... |
|
| Back to top |
|
|
sthirumalai
New User
Joined: 14 Aug 2007
Posts: 14
Location: Chennai
|
|
|
|
Bill, You are correct
If there is a matching SNO from TABLE1 in TABLE 2 and no DATE_FIELD_02 table2 is NULL then the query should pick that SNO alone.
Otherwise, how would 2 be selected? |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Try this
| Code: |
SELECT A.SNO
FROM TABLE1 A,TABLE2 B
WHERE A.SNO=B.SNO
AND B.DATE_FIELD_02 IS NULL; |
|
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Pandora-Box wrote: |
Try this
| Code: |
SELECT A.SNO
FROM TABLE1 A,TABLE2 B
WHERE A.SNO=B.SNO
AND B.DATE_FIELD_02 IS NULL; |
|
Pandora-Box,
Are you sure this will give '2'? Your sql will give ONLY '1'. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Arun,
Ooopps If Op needs 2 This should be a subquery within like below
| Code: |
Select Sno from table1
where not exists (SELECT A.SNO FROM TABLE1 A,TABLE2 B WHERE A.SNO=B.SNO AND B.DATE_FIELD_02 IS NULL) |
|
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Hi Pandora,
Still there is no need to JOIN TABLE1 and TABLE2 in the sub select. As told earlier, a correlated subquery might be better than joining the table again.
Something like this,
| Code: |
SELECT SNO FROM TABLE1 A
WHERE NOT EXISTS (SELECT 1 FROM TABLE2 B
WHERE A.SNO=B.SNO
AND B.DATE_FIELD_02 IS NULL) |
|
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| ;-) Agreed |
|
| Back to top |
|
|
|
|
