Bit of MySQL help needed with JOINS

[Django x2]

Hi all, I've peaked here with SELECT * FROM PRODUCTS as that is about as far as I seem to be able to go with SQL so hoping someone can help

Simply, we have 4 tables:

Products (which uses integers to reference data in other tables - this is the daddy table)
Type (the type of product with an ID and Value field)
Manufacturer (the manuf of the product with an ID and Value field)
Model (the model of the product with an ID and Value field)

Say that in Type you had
1, Car
2, Bus
3, Lorry

In Manufacturer you had
1, Volvo
2, Ford
3, Scania

and in Model you had
1, XC90
2, V70
3, FH16
4, R-Series

in Products would be
1, 1, 1 (which would relate to Car, Volvo, XC90)
3, 1, 3 (which would be Lorry, Volvo, FH16)
3, 3, 4 (which would be Lorry, Scania, R Series)

How can I get the JOIN query to give me that result instead of the integer?

Thanks

 

[touch]

SELECT Type.Value, Manufacturer.Value, Model.Value FROM Products
JOIN Type ON Products.TypeId = Type.Id
JOIN Manufacturer ON Products.ManufacturerId = Manufacturer.Id
JOIN Model ON Products.ModelId = Model.Id

edit: removed table alias names to make the query easier to understand. This is what you'd write it like once you've got the hang of it:

Spoiler

SELECT t.Value, ma.Value, mo.Value FROM Products p
JOIN Type t ON p.TypeId = t.Id
JOIN Manufacturer ma ON p.ManufacturerId = m.Id
JOIN Model mo ON p.ModelId = mo.Id

The bits in bold are where i've given each table alias names so I dont have to type out the entire table name each time I want to reference it.

 

[Django x2]

Thanks Touch, I actually managed to work it out just as you posted. Thanks all the same

SELECT t.Value, ma.Value, mo.Value FROM Products p
JOIN Type t ON p.TypeId = t.Id
JOIN Manufacturer ma ON p.ManufacturerId = m.Id
JOIN Model mo ON p.ModelId = mo.Id

 

[touch]

On another point, your tables are not quite normalised correctly and there will be duplication of data.

For example, if you have 5 Volvo XC90s in your products, every row will contain the same entries for ModelId, ManufacturerId and TypeId because each model will always have the same manufacturer and type.
You could move these ID fields into the model table.


Then each product would only need to store a ModelId because if you know what model it is, you know all the other info too.

(you might need to adjust to suit exactly how your data will be stored. Maybe if you have a product like a VW Transporter it might be a type Van or Bus?)

 

[Django x2]

Cool, that makes sense. Thanks