CODEWITHSUNDEEP
sqlpostgresql
Lagani
Lagani

Reputation: 107

Postgres: select data from table based on few clauses on relationship table

Let's say we have two tables:

person (
    id int,
    name varchar,
    surname varchar
)

vehicle (
    id int,
    make varchar,
    model varchar,
    has_gps boolean,
    has_ac boolean
    person_id
)

I want to select all persons who have at least one vehicle that has GPS (has_gps = true) and at least one vehicle that has AC (has_ac = true).

So basically person must have minimum of 2 vehicles, but there must be at least one vehicle that has GPS and at least one vehicle that has AC.

I tried with exists, but I can't seem to figure out how I could do this in postgres.

For example:

Person (1, 'Michael', 'Jordan')
Person (2, 'Leo', 'Messi')

Vehicle (1, 'bmw', 'x5', true, false, 1)
Vehicle (2, 'ferrari', 'testarossa', false, true, 1)
Vehicle (3, 'mercedes', 's class', true, true, 2)

In the results I should only get Person with ID 1, because it has at least one vehicle with gps and at least one with ac.

Upvotes: 0

Views: 213

Answers (2)

Oto Shavadze
Oto Shavadze

Reputation: 42753

Something like :

select person_id from vehicle
group by person_id
having
count(case when has_gps then 1 end ) > 0
and
count(case when has_ac then 1 end ) > 0
and
count(*) > 1

?

Upvotes: 1

Arulkumar
Arulkumar

Reputation: 13237

Using GROUP BY with HAVING give the expected result:

SELECT person_id
FROM vehicle
WHERE has_gps = true OR has_ac = true 
GROUP BY person_id
HAVING COUNT(*) > 1

Demo on db<>fiddle

Upvotes: 0

Related Questions