-1

My question is based on the answer from asemprini87 from this link:How to Join 4 tables in SQL. (please check the original tables through this link, if you are interested).

My question focuses on: how to understand the last join, and why the syntax is right: the two tables(p, pe) at the last join on condition, not related to based table (f)?

Because I also face the same problem: there are 4 tables, but no one table related to the other three, I found the answer from that link, the answer is correct, but I don't understand why it can link like that.

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

Thank you.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Annie
  • 85
  • 3
  • 14
  • perhaps: idProduto and idEncomenda both have same value.. it looks like a bad table design but doesn't mean there isn't an intention for these relationships. – Steve Tomlin Sep 25 '20 at 19:43
  • Thanks. I might not explain my question very clearly before, and edited again. – Annie Sep 25 '20 at 19:50
  • Please put what is needed to ask your question in your post, not just at a link. – philipxy Sep 25 '20 at 21:50
  • Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. – philipxy Sep 25 '20 at 21:50
  • This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy Sep 25 '20 at 21:52

2 Answers2

1

When making multiple joins, not all of the tables need to relate to a single table, there just needs to be a chain of relationships that link each additional table to one of the previous tables.

Let's look at a simpler example where we have 4 tables: A, B, C, and D.

  • A is related to B
  • B is related to C
  • C is related to D

So, tables C and D aren't directly related to A, but through the chain of relationships we can find the correct rows across all the tables with a process something like this:

  1. Take a set of rows from table A
  2. Use the relationship between A and B to the related rows from table B
  3. Use the relationship between B and C to find the related rows in table C
  4. Use the relationship between C and D to find the related rows in table D
DavidP
  • 613
  • 5
  • 12
  • Yes, the relationship is exactly as what you described. Your first sentence points out the key confused part of my understanding. – Annie Sep 25 '20 at 20:24
1

My question focuses on: How to understand the last join, and why the syntax is right: the two tables(p, pe) at the last join on condition, not related to based table (f)?

Its related to (f) only through the other tables...not directly.

The last JOIN connnects Producto(p) and Produto_Encomenda(pe) on idProducto

Encomenda(e) is joined to Produto_Encomenda (pe) on idEncomenda

finally Fornecedor(f) is joined to Encomenda(e) ON idFornededor

Chris Catignani
  • 5,040
  • 16
  • 42
  • 49
  • @Annie Your welcome...any amount of time learning TSQL is well spent...https://www.w3schools.com/sql/default.asp Good luck and welcome to SO – Chris Catignani Sep 26 '20 at 04:40