Write a Simple Query... if u can.
-
HI ALL!
Please Look at this Table:-----------------------------------------
ID NAME YN
------------------------------------------
1 A Y
1 B Y
1 C Y
1 D N
1 E Y
1 F Y
1 G NULL
2 H Y
2 I Y
2 J NULL
3 K Y
3 L Y
3 M N
3 N Y
4 O Y
4 P Y
4 Q Y
---------------------------------------
Now write a query that will fetch only those record for which YN Value is Both "Y and "N" for same ID.
I mean for same ID, I need Both Y and N value in YN Field. For same ID if YN value is only Y or N then filter those records.
Output should be:
-----------------------------------------
ID NAME YN
------------------------------------------
1 A Y
1 B Y
1 C Y
1 D N
1 E Y
1 F Y
3 K Y
3 L Y
3 M N
-----------------------------------------
-
Try this:
SELECT t1.*
FROM table1 t1
JOIN (SELECT id
FROM table1
WHERE yn = 'Y'
INTERSECT
SELECT id
FROM table1
WHERE yn = 'N') t2
ON t1.id = t2.id
P.s. it won't work on SQL 2000 or earlier
Markus
[font="Verdana"]Markus Bohse[/font]
-
I see Markus' query returns NULL... which I assume you don't want... and is handled here below with the inequality B.YN <> C.YN in the derived table:
SELECT
A.ID,A.NAME, A.YN
FROM TABLE1 A JOIN
(SELECT DISTINCT B.ID, B.YN
FROM TABLE1 B
JOIN TABLE1 C ON B.ID = C.ID
WHERE B.YN <> C.YN) D
ON
A.ID = D.ID AND A.YN = D.YN
ORDER
BY A.ID, A.NAME, A.YN
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012) -
Todd,
just out of interest. In my tests my query did not return NULL. Which test data where you using? You sure you run it on SQL 2005?
Markus
[font="Verdana"]Markus Bohse[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply