Complex MySQL Query

bailo81

I have two databases one with my main 'product' data and one with features.

For my example i will call them 'cars' and 'carFeatures'

So i have a search where users can search for cars, they select the features and it only returns cars with all the features.

So in my example, the user has selected electric windows (id = 34) and air conditioning (id = 21). Now one car has both these features.

This is the select query

SELECT * FROM cars c 
JOIN carFeatures cf ON c.carId = cf.carId
WHERE cf.feature = '21' 
AND cf.feature = '34'
GROUP BY c.carId

Its not returning any rows

ahundiak

The query is failing because the joined record still only has one feature column and it will never have two different values.

I'm not sure how to solve this particular problem. Seems like something that should be easy.

You might need to do multiple queries and just refine the results. Get all the cards with electric windows then do second query for all the cars in the first set that have air conditioning. But there should be a cleaner way.

shu

I am a Postgres user, so the syntax might not be the same as MySQL, maybe this example can help:

SELECT y.* FROM (
   (SELECT carID, COUNT(carID) AS num FROM carFeatures WHERE feature IN ('21', '34') GROUP BY carID) AS x 
INNER JOIN 
   (SELECT * FROM cars) AS y
ON x.carId=y.carID
) WHERE x.num=2

Of course, on x.num=2, the number 2 will change depending on how many features the use selected!

Cheers...

Shrike

I don't even see the need for the group by, unless you need cars which do have feature X but not feature Y.

SELECT * FROM cars c 
JOIN carFeatures cf ON c.carId = cf.carId
WHERE cf.feature IN ('21' ,'34' )

Should be sufficient?

shu

Shrike wrote:
I don't even see the need for the group by, unless you need cars which do have feature X but not feature Y.
SELECT * FROM cars c 
JOIN carFeatures cf ON c.carId = cf.carId
WHERE cf.feature IN ('21' ,'34' )
Should be sufficient?

Ah, NO! My solution assumes his tables cars and carFeatures have a one to many relationship. That's why he needed the GROUP BY to eliminate duplicate records returned. And a simple JOIN will not give him the correct result because a particular carID has to have corresponding entries for ALL features selected in the carFeatures table!

Shrike

Whoops, yes 🙂