CODEWITHSUNDEEP
sqlpostgresqlselectsyntax
codebot
codebot

Reputation: 697

Multiple embedded AND in the Where part of a Select in PostgreSQL

I use PostgreSQL 10 and I have the following table. 

id  name   place
1    20     56
2    21     51
3    21     53
4    21     54

the select I want to do is something like

select id from mytable where place = 51 and place=53 and place=54 and name = 21

I have to end up with id 2 3 4 but I can only use place and name .

I also tried select id from mytable where (place = 51 and name = 21) and (place=53 and name = 21) and (place=54 and name = 21)

but I keep getting an empty set of results.

How can I syntax that?

Thank you

Upvotes: 0

Views: 40

Answers (2)

Neil B
Neil B

Reputation: 2224

The reason you get empty results is because you have no records where place equals both 51 and 53. Obviously place can only be one value per record.

I assume you are trying to return any results that match any of your criteria? For that try this:

SELECT id FROM mytable WHERE place IN (51, 53, 54) OR name IN (21);

If you want to exclude any results where name is not 21 then use AND instead.

SELECT id FROM mytable WHERE place IN (51, 53, 54) AND name = 21;

OR means return records if it matches either 'condition a' or 'condition b'. AND means return record only if it matchs 'condition a' AND (as well as) 'condition b'

IN means return record if it matches any of the values IN the list.

Upvotes: 1

Ranidu
Ranidu

Reputation: 19

Try this

SELECT id
FROM my_table 
WHERE place IN (51,53,54) AND name = 21

Upvotes: 2

Related Questions