Michael Michael - 11 days ago 10
MySQL Question

SQL puzzle very close to solving but can't get parentid

I need a bit of help/direction as to how to write an SQL query using the following information:

Please click here for the SQL table

The Question is:

The manager would like to see all of the cars currently in the showroom and their price, in the following form: [Make] [Model] – £[Price] e.g. “VW GOLF - 3000”. Write a SQL query to satisfy this.

I can't seem to get the parentCarid to match the ID in the car table and this is the closest ive gotten to making it happen.

SELECT Car.Makecode, Car.Name, Showroom.Price
FROM Car
INNER JOIN Showroom
ON Car.Id=Showroom.CarID
WHERE Showroom.CarId = “2” OR Showroom.CarId = “5” OR Showroom.CarId = “8”;


But this still doesn't display the Model name it only displays model code and I'm thinking i would need to somehow join the Car table with itself to get the answer.

So I want to display it like this in a way :

Model Make Price

Ford Fiesta - 4000
VW Golf - 3000
Peugeot 307 -5000

Answer

You could try this query:

SELECT c.Id, p.Name, c.Name, s.Price
FROM Car c 
     INNER JOIN Car p ON p.Id = c.ParentCarId
     INNER JOIN Showroom s ON s.CarId = c.Id