Let's say I have two tables. One is a table with car property and the second one is a table with car options. I join the two tables and want to select the car that has a Subwoofer as per the query below.What if I want to select the car that has the Subwoofer option but also want to display all the options that this car has along with the subwoofer option.
The problem I have is that when I use a query like below with the WHERE clause, only the subwoofer is shown in the car options but I need all of the car options displayed. Also I need to search by car options with the php and html forms so I cannot change the Car_Options.Name in the WHERE clause. Any ideas how to do that?
FROM Cars JOIN Car_Options ON Car_Options.IdCar=CarName.IdCar
GROUP BY CarName
SELECT CarName, Adress, Price, Description, GROUP_CONCAT(Car_Options) as AllOptions FROM Cars JOIN Car_Options ON Car_Options.IdCar = CarName.IdCar WHERE EXISTS (SELECT 1 FROM Car_Options CO WHERE CO.IdCar = CarName.IdCar AND CO.Name = "Subwoofer") GROUP BY CarName
We join the Cars table with the Car_Options table, then remove groups of rows which don't have a Subwoofer associated row. We concatenate the Car_Options into a single field.