0

Say I have two tables, 'cars' and 'properties', linked by table 'car_properties'. I can select all cars that have say, electric windows OR radios OR both, doing something like the following...

SELECT cars.id 
FROM cars 
JOIN car_properties 
   ON cars.id = car_properties.car_id 
WHERE 
   car_properties.property_id = 1 
   OR car_properties.property_id = 2

where the property_id of 'electric windows' would be '1' and 'car radio' would be '2'.

BUT, what I really want to do is select ONLY cars that have BOTH properties (not just one or the other or both).

I suspect this is really simple, but what's the most efficient way to do it?

Carth
  • 2,303
  • 1
  • 17
  • 26
Richt222
  • 165
  • 1
  • 1
  • 7
  • Please format your SQL code. Don't just put the whole thing in one long line. – ObscureRobot Nov 07 '11 at 01:11
  • 1
    You will find a plethora of answers for this exact situation here: http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation/7774879#7774879 – Erwin Brandstetter Nov 07 '11 at 01:22
  • Your question suggests that the structure of your car_properties table is car_properties{property_id, value}. If this is the case you would likely find it advisable to redesign this so that each unique attribute (property ID) is a separate column. It will help you avoid situations like this. – Carth Nov 07 '11 at 01:30
  • @ObscureRobot Quite right. Sorry. – Richt222 Nov 07 '11 at 10:21
  • @Carth Thanks, but I don't really think that's suitable for my particular app. – Richt222 Nov 07 '11 at 10:26
  • @ErwinBrandstetter That's exactly the kind of thing I'm looking for, thanks! – Richt222 Nov 07 '11 at 10:27

1 Answers1

2

JOIN twice:

SELECT cars.id
  FROM cars
  JOIN car_properties AS cp1
    ON cp1.car_id = cars.id
   AND cp1.property_id = 1
  JOIN car_properties AS cp2
    ON cp2.car_id = cars.id
   AND cp2.property_id = 2
;
ruakh
  • 175,680
  • 26
  • 273
  • 307