CODEWITHSUNDEEP
mysqlsqlpostgresqlrelational-database
Tyrel Denison
Tyrel Denison

Reputation: 476

SQL query for to find item with a specific attribute and related non-foreign keyed items

I am trying to write a sql query to return a list of items that have a specific attribute and that are also referenced by a non-foreign-keyed item. To expound, let's say table one is cars, and it has a registration status. Table two is parking permits, which has a non-foreign-keyed field of car_id that is an int representing car ids. I want to return a list of cars that have a registration status of 'active' and at least one permit bearing its id.

I've tried different combinations of inner and left joins combined with wheres, but I can't get it to return what I am needing.

Would there be a way to count the number of permits returned by a join for each car? Or is there a simpler way that I am missing?

Upvotes: 2

Views: 1590

Answers (2)

Kickstart
Kickstart

Reputation: 21533

You can do a JOIN and count the matching ids:-

SELECT a.car_id
        COUNT(b.permit_id)
FROM cars a
INNER JOIN parking_permits b
ON a.car_id = b.car_id
WHERE a.registration_status = 'active'
GROUP BY a.car_id

If you wanted to include those without any parking permits in the list:-

SELECT a.car_id
        COUNT(b.permit_id)
FROM cars a
LEFT OUTER JOIN parking_permits b
ON a.car_id = b.car_id
WHERE a.registration_status = 'active'
GROUP BY a.car_id

Upvotes: 0

Robert Kock
Robert Kock

Reputation: 6028

Something like:

SELECT cars.*
FROM cars
WHERE cars.reg_status='active'
AND EXISTS (SELECT *
FROM permits
WHERE permits.car_id=cars.car_id);

This should return all cars with status active and having at least 1 permit

By the way: if the 'permits' table refers to the 'cars' table by means of a car_id, I strongly recommend to create a foreign key

Upvotes: 2

Related Questions