Search code examples
sqlmany-to-manyoracle-sqldeveloper

Handling a many to many relationship in sql

I have two main tables. CARS and FEATURES. A car have many features and a feature can have many cars. Therefore a bridge table sits in between them called CARFEATURE. It has the PKFKCarID and PKFKFeature ID. I want to query cars that only have the specific features I assign. How would I do this with SQL.

I've tried, SELECT * FROM CARFEATURE WHERE FEADTUREID = 'GPS' AND FEATUREID = 'RADIO'. BUT i end up getting null result.

Solution

  • Various options. I'd guess that you want

    select carID
  from carFeature
 where featureID in ('GPS', 'Radio')
 group by carID
having count(distinct featureID) = 2

    You could also do

    select carID
  from carFeature
 where featureID = 'GPS'
intersect
select carID
  from carFeature
 where featureID = 'Radio'

    The latter query is a bit clearer in my opinion but it's probably not as efficient as the first.