Michael Michael - 1 year ago 85
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
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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download