coder_1432 coder_1432 - 3 months ago 7
MySQL Question

Select Include all data after WHERE clause

Good afternoon,

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?

SELECT CarName,Adress,Price,Description,Car_Options.Name
FROM Cars JOIN Car_Options ON Car_Options.IdCar=CarName.IdCar
WHERE Car_Options.Name="Subwoofer"
GROUP BY CarName

Answer
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.

Comments