J
J
Jsman2020-11-27 20:25:15
PostgreSQL
Jsman, 2020-11-27 20:25:15

How to check if a record exists?

Good afternoon! There was a question how to check up existence of records passing id of records in IN.

The cities table (contains information, about the city)
The country table (information, about the country).
Table T3 which has a mapping between country and cities. 

The cities table has 2 records 
id: 1, name: Moscow
id: 2, name: Penza

The country table has 1 record
id: 1, name: Russia

The T3 table has 1 record
id:1, id_city:1, id_country:1

I thought you can do this 

select exists(select * from T3 where id_сountry=1 AND id_city IN (1, 2));

BUT it will find id_city 1 and say try, but you need a solution so that it returns false, because there is no id_city = 2 in T3. Please tell me

a solution or parting words.

Answer the question

In order to leave comments, you need to log in

2 answer(s)
J
Jsman, 2020-11-29
@SeniorDmitry

select (select count(DISTINCT id_city) from T3 where id_country=1 AND id_city IN (1, 2)) = 2;

M
Melkij, 2020-11-27
@melkij

Well, for example,

select exists(
select from unnest(array[1,2]) as city where not exists (select from T3 where id_сountry=1 AND id_city = city)
)

select exists(values(1),(2) except select id_city from T3 where id_сountry=1 AND id_city IN (1, 2));

select (select count(*) from T3 where id_сountry=1 AND id_city IN (1, 2)) = 2; -- где = 2 - число элементов в in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question