(My)SQL: Join multiple tables

Question

I'm new to (My)SQL and need some help: In a database I have 3 tables, an example:

purchases:

id model price
------------
1   1  2500
2   2  14000

manufacturer:

id  name
-----------
1   Opel
2   Crysler

model:

id  name    manufacturer  
-------------------------
1   Astra       1
2   C 300       2

In the table purchases model is a foreign key and is linked to model.id. In model manufacturer is a foreign key and is linked to manufacturer.id.

My aim is a table which looks like below:

id model   price  manufacturer
------------------------------
1   Astra  2500   Opel
2   C300   14000  Chrysler

I know how to do a JOIN in the table purchases to get the model's name. Unfortunately I don't know how to get the manufacturer?

My SQL-query:

SELECT * FROM purchases
JOIN model
ON purchases.model = model.id

Edit your question and include the query that you do know how to write. As a hint: the ultimate query is just one more `JOIN`. — Gordon Linoff, May 08 '16 at 12:33
@GordonLinoff I edited my question. My approach would be something like (for the second JOIN): `JOIN manufacturer ON purchases.name = manufacturer.id` , however for SQL purchaes.name is an unknown clause. — brahue, May 08 '16 at 12:42

score 2 · Answer 1 · edited May 23 '17 at 12:01

2

Try it with 'inner join':

select m.name as model, p.price, mf.name as manufacturer
from purchases p 
inner join
model m on p.model = m.id 
inner join
manufacturer mf on m.manufacturer = mf.id;

You can join your tables with 'where'.

Try it:

 select m.name as model, p.price, mf.name as manufacturer
 from purchases p, manufacturer mf, model m
 where m.manufacturer = mf.id
 and p.model = m.id

But with the "where" is not very good, and considered a archaic way.

There is a question similar with your, perhaps might help you to clarify the question: SQL Inner-join with 3 tables?

edited May 23 '17 at 12:01

Community

1
1

answered May 08 '16 at 12:39

Leonardo Lacerda

397
1
3
15

@PaulSpiegel I do not forgot this condition. Please review the text . – Leonardo Lacerda May 08 '16 at 12:58
Sorry, i've allready deleted my comment, before you posted yours. However i probably wouldn't oversee that condition, if you would use `JOIN .. ON ..`. – Paul Spiegel May 08 '16 at 13:01
1

I'm not downvoting the answer, but the upvote is totally inappropriate. Don't use commas in the `FROM` clause. This is an archaic syntax that has been replaced by the more powerful `JOIN` and `ON` keywords -- for many, many years. – Gordon Linoff May 08 '16 at 14:21
@GordonLinoff I updated my answer. Thank you for your Collaboration – Leonardo Lacerda May 08 '16 at 20:42

score 0 · Answer 2 · answered May 08 '16 at 14:19

0

The proper way to join tables is using the JOIN (and related) keywords. Simple rule: Never use commas in the FROM clause. Always use explicit JOIN syntax:

 select m.name as model, p.price, mf.name as manufacturer
 from purchases p JOIN
      model m
      on p.model = m.id
      manufacturer mf
      on m.manufacturer = mf.id;

answered May 08 '16 at 14:19

Gordon Linoff

1,242,037
58
646
786

Your comment doesn't relate to the question, because the OP is already using the JOIN syntax. BTW: the second JOIN keyword is missing. – Paul Spiegel May 08 '16 at 14:33
@PaulSpiegel . . . The original version of the question did not have a sample query. And, the other answer was only using commas when I write this. – Gordon Linoff May 09 '16 at 01:23

(My)SQL: Join multiple tables

2 Answers2