(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
– Gordon Linoff, Commented May 8, 2016 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
– brahue, Commented May 8, 2016 at 12:42

Community · Accepted Answer · 2017-05-23 12:01:37Z

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, 2017 at 12:01

CommunityBot

11 silver badge

answered May 8, 2016 at 12:39

Leonardo Lacerda

3971 gold badge3 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Leonardo Lacerda Over a year ago

@PaulSpiegel I do not forgot this condition. Please review the text .

Paul Spiegel Over a year ago

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

Gordon Linoff Over a year ago

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.

Leonardo Lacerda Over a year ago

@GordonLinoff I updated my answer. Thank you for your Collaboration

Gordon Linoff · Accepted Answer · 2016-05-08 14:19:36Z

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 8, 2016 at 14:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Paul Spiegel Over a year ago

Your comment doesn't relate to the question, because the OP is already using the JOIN syntax. BTW: the second JOIN keyword is missing.

Gordon Linoff Over a year ago

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

Collectives™ on Stack Overflow

(My)SQL: Join multiple tables

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related