Hard to explain SELECT question 1

[markj11]

TableA A B
1 X
1 Y
2 X

I want my results to be 1 X because B = Y.

This is kind of what I was tring:

SELECT	DISTINCT A,
	CASE 	WHEN B = 'X' THEN
		   B
		ELSE
		   ''
		END
	AS B
FROM
	tableA
WHERE
	B = 'Y'

 

[SQLBill]

Maybe.......

SELECT DrvA.A, DrvA.B
FROM (SELECT A,B
      FROM TableA
      WHERE B = 'Y') DrvA
WHERE DrvA.B <> 'Y'

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Maybe...

SELECT TblA.A, TblA.B
FROM TableA TblA
 LEFT OUTER JOIN (SELECT A
                  FROM TableA
                  WHERE B = 'Y') TblB
   ON TblA.A = TblB.A
WHERE TblA.B <> 'Y'

-SQLBill

Posting advice: FAQ481-4875

 

[markj11]

Thank you. That would work great but I just realized the table is like this
A B C
1 X AA
1 Y BB
2 X AA

and I want 1 BB because B = X

 

[markj11]

Thanks, A varaition of the last SQL will do just fine.