How to filter rows based on multiple conditions in sql?

Question

I have 2 columns in a table ID and name

ID Name
1 A
1 B
1 C
2 B
2 C
3 A
3 B

I want the result set as if the value A is present then only that row should be shown, if not all the other values should be shown.
Result

1 A
2 B
2 C
3 A

Answer 1

Try this:



select distinct ID, Name

from YourTable

where Name='A'

UNION

select distinct a.id, a.name

from YourTable a

left join YourTable b on a.id = b.id and b.name='A'

where b.id is null

order by id, name



Enjoy :)

Answer 2

you somewhat should not be doing this style of string manipulation in sq. itself. that is larger to do this style of ingredient interior the shopper code (for courses). yet once you want a speedy and grimy answer each and each version of sq. has some thing which will do the trick. that's an answer for MSSQL. declare @chvResult NVARCHAR(MAX) go with @chvResult = ISNULL(@chvResult + ', ' + chvValue, chvValue) FROM dbo.tb_Table go with @chvResult

Answer 3

Another method (probably less efficient wrt processing resources)



SELECT ID, Name FROM someTable a

WHERE Name = 'A' OR NOT EXISTS

(SELECT 1 FROM someTable b

WHERE a.ID = b.ID AND b.Name = 'A')

Answer 4

George beat me too it. My solution was very similar, but with a nested select rather than left join.



SELECT ID,NAME

FROM IDNAMES

WHERE ID NOT IN (select ID from idnames where name = 'A')



UNION



SELECT DISTINCT ID,'A'

FROM IDNAMES

WHERE NAME = 'A'