How to Join 4 tables in SQL

Question

What I'm trying to do is to select all 4 of those tables with join but i can't figure it out how because there isn't a table connected to all of the others.

  create table Encomenda(
    idEncomenda int identity,
    idFornededor int not null,
    estado varchar not null,
    Constraint pk_Encomenda Primary Key (idEncomenda),
    );

    create table Produto_Encomenda(
    idProduto_Encomenda int identity,

    idProduto int not null,
    idEncomenda int not null,
    quantidade int not null,

    constraint pk_Produto_Encomenda Primary Key (idProduto_Encomenda),
    constraint fk_Produto foreign key (idProduto) references Produto (idProduto) ,
    constraint fk_idEncomenda foreign key (idEncomenda) references Encomenda (idEncomenda) ,
    );
    create table Fornecedor(
    idFornecedor int  identity,
    nomeFornecedor varchar(60) not null,
    moradaFornecedor varchar(60) not null,
    contactoFornecedor int not null,
    constraint pk_Fornecedor Primary Key (idFornecedor),
    );
    create table Produto(
    idProduto int identity,
    nomeProduto varchar(60) not null,
    quantidadeExistenteProduto int not null,
    precoUnidade float not null,
    Constraint pk_produto Primary Key (idProduto),
    );

I was trying to make a join between the 4 of them and what I would like to show/select are:

Fornecedor.nomeFornecedor, idEncomenda, Produto.nomeProduto and Produto_encomenda.quantidade" joined toguether where 
Produto.idproduto = produto_Encomenda.idproduto
Fornecedor.idFornecedor = Encomenda.idFornecedor

I don't think I can explain better but in the end I wanted to select a table that containsFornecedor.nomeFornecedor, idEncomenda, Produto.nomeProduto and Produto_encomenda.quantidade, but because the 4 tables dont have 1 common table im lost in how to make the join :\ im probably just tired as hell but if someone could help me i would apreciatte cuz im so lost here

Please read [How do I ask a good question](http://stackoverflow.com/help/how-to-ask). Your code needs to be within a Code Sample area and your question is one that could be answered easily with a modicum of research. — Ethilium, Mar 06 '17 at 16:14
It would also help to put your create-table sample code within a code block so that it is formatted in a readable way. — Jason Spake, Mar 06 '17 at 16:38

asemprini87 · Accepted Answer · 2017-03-06T17:09:00.310

Ok, now that I think I better understand the question you need the following fields: Fornecedor.nomeFornecedor, idEncomenda, Produto.nomeProduto and Produto_encomenda.quantidade.

So, let's see if this works:

SELECT f.nomeFornecedor, 
e.idEncomenda, 
p.nomeProduto,
pe.quantidade
FROM Fornecedor as f
INNER JOIN Encomenda AS e
ON f.idFornecedor = e.idFornededor 
INNER JOIN Produto_Encomenda as pe
ON e.idEncomenda = pe.idEncomenda
INNER JOIN Produto as p
ON p.idProduto = pe.idProduto

I think this should work

score 0 · Answer 2 · answered Mar 06 '17 at 16:07

0

You join tables using the 'JOIN' statement. There are four types:

INNER - Only join where a match is found.

LEFT - Only join where a match is found in the right hand table, but join the whole of the left.

RIGHT - Only join where a match is found in the left hand table, but join the whole of the right.

FULL OUTER - Join both tables together, even where no match is found.

A basic JOIN goes like this:

INNER JOIN MyTable ON MyTable.ID = SomeTable.ID

You should read this.

Hope it helps!

answered Mar 06 '17 at 16:07

Skyra

49
4

That's actually not true. You can perfectly do a Join doing "FROM table1, table2 WHERE table1.id2 = table2.id2". It is true that using the "JOIN" is clearer for later, but it is not the only way of doing the JOIN – asemprini87 Mar 06 '17 at 16:13
@asemprini87 the old style of joining is to be avoided. Not only is it not any clear to read at all, especially when joining on more than 2 tables, but is gets really hard when you need to join on more than one condition, and it gets totally unreadable when you have where clause not related to the join. There is no way to know which where is for the join and which is not. – GuidoG Mar 06 '17 at 16:18
2

@asemprini87 while correct, I wouldn't recommend anyone to use implicit joins in this century, since explicit joins are a part of ANSI-SQL for over 20 years now... – Zohar Peled Mar 06 '17 at 16:18
I'm not saying someone should use it. I'm only saying that it is not the only way of doing a join. The first time I saw it on a legacy database I was like... "What is the hell is this?". It is good to know what you might find specially if you start working on this. – asemprini87 Mar 06 '17 at 16:21
1

And to be precise the comma separated list of tables is a cross join. It behaves like an inner join when a where predicate is added to filter out rows that don't match. – Sean Lange Mar 06 '17 at 17:07

How to Join 4 tables in SQL

2 Answers2

Linked