-5

An INNER JOIN between two tables is pretty straightforward. It shows all records of these two tables that have the same value in the fields of their foreign key field pair. in Fig 1 the foreign key field pair is (table1.field_e, table2.field_a). The solution is represented by the dark blue area, which corresponds to the records of table1 that have a field pair link to table2.

enter image description here

But what if I want to INNER JOIN across multiple tables? Fig 2 shows an example of four tables linked by three foreign key field pairs. What would be the script for a solution that shows only the records of table1 that are linked up all the way to table4?

Steven
  • 289
  • 2
  • 5
  • 14
  • 2
    _"What would be the code for a solution that shows only the records of table1 that are linked up all the way to table4?"_ - it would be four separate `INNER JOIN` operators, all with `ON` equi-join criteria. – Dai Oct 16 '22 at 09:41
  • 1
    [learnsql.com/cookbook/how-to-join-multiple-3-plus-tables-in-one-statement](https://learnsql.com/cookbook/how-to-join-multiple-3-plus-tables-in-one-statement/) – Stu Oct 16 '22 at 10:10
  • @stu The link you sent is only for linking table1 to table2, table1 to table3, and table1 to table4. It uses table1 as "hub". That is not the case in the question. – Steven Oct 16 '22 at 10:17
  • Fig. 2 shows incorrectly. https://i.stack.imgur.com/IyTkF.png – Akina Oct 16 '22 at 10:23
  • [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) [mre] [ask] [Help] – philipxy Oct 16 '22 at 10:24
  • Does this answer your question? [Required to join 2 tables with their FKs in a 3rd table](https://stackoverflow.com/questions/23839392/required-to-join-2-tables-with-their-fks-in-a-3rd-table) – philipxy Oct 16 '22 at 10:25
  • [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) – philipxy Oct 16 '22 at 10:28
  • 1
    [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/q/333952) – Akina Oct 16 '22 at 10:34
  • 2
    Do you want us to work on your problem, but you don't want to take part in this work yourself? I doubt that anyone will help you. – Akina Oct 16 '22 at 10:36
  • [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] – philipxy Oct 16 '22 at 10:53
  • 3
    Your problem is now both unclear and ambiguous. You must provide an example which is clear and adequate. This is your part of work. See the link provided earlier and also [Tips for asking a good Structured Query Language (SQL) question](//meta.stackoverflow.com/questions/271055). – Akina Oct 16 '22 at 10:53
  • 1
    What is the legend for that diagram? What does it supposedly say & how is it relevant to the rest of the post? What do the circles encircle? Are they sets of values? But SQL tables are not sets, they are bags/multisets. Until you explain how to read the diagrams exactly or say what they supposedly say you have said nothing by giving them. PS Please do not ask us to write your code for you. Explain how you are stuck. PS When clear this will be a duplicate of many easily googled beginner Q&A. – philipxy Oct 16 '22 at 10:56
  • "It shows all records of these two tables that have the same value in the fields of their foreign key field pair." No, inner & outer joins are on any condition & not on equlity of FK-PK subrows per se. This is explained in the answers at the proposed duplicate link. Constraints, including FKs & PKs, need not be known, be declared or exist to query. What exactly does "INNER JOIN across multiple tables" mean? (Emphasis does not make anything clearer.) – philipxy Oct 16 '22 at 11:20
  • The method of using diagrams to explain an SQL situation is directly derived from W3 (https://www.w3schools.com/sql/sql_join_inner.asp). Who am I to reject it. I did improve on it by adding the script frames. As to "work" done, have you any idea what time went into creating those diagrams? – Steven Oct 16 '22 at 12:34
  • W3schools is a poor resource. Moreover they limit their use to Venn diagrams (2 circles), moreover they don't explain themselves either & Venn diagrams are for sets but SQL tables are bags. Neither you nor they have given a legend for what the diagrams are supposed to mean. You say you read those diagrams, so what did you think their legend & meaning is? ["You are doing just what thousands of others have done--got a *vague impression* you (wrongly) *assume* makes sense."](https://stackoverflow.com/a/55642928/3404097) And where & how are you 1st stopped applying what they say in your case? – philipxy Oct 16 '22 at 22:09
  • [How to select from two tables in MySQL even if not all rows in one table have corespondents in the other?](https://stackoverflow.com/q/3969452/3404097) – philipxy Oct 16 '22 at 23:03
  • @Akina See my comments & the Q&A & comments they lead to re problems with the asker's diagrams & Venn diagrams & so your diagram. What is the legend for yours? What do your circles encircle? What areas do the join texts label? What if an input or output is a bag? (If yours is a Venn & intersection of 2 circles are the rows of an inner join then the circles are not input tables as labelled but output tables for certain outer joins & only for certain cases with limitations on duplicates & nulls on input & output. And since circles aren't inputs adding a 3rd doesn't make sense.) – philipxy Oct 16 '22 at 23:56
  • @Akina Your diagram could be a Venn diagram where an area is associated with a table & holds the set of non-null values that appear in the field_a column of the table. That diagram reflects the fact that for joins on a particular column the set of non-null values in a column for an output is the intersection of those of the inputs. But then the binary joins need to be labelling 2-circle intersections & the 3-join just the intersection of all 3. And of course it just gives the set of values input & output, not the rows, and only for all joins on that column. – philipxy Oct 17 '22 at 00:29

1 Answers1

-2

Use "derived tables". Make a first derived table dt1 by linking table1 to table2. Then make a second derived table dt2 by linking dt1 to table3. Finally, make a third derived table dt3 by linking dt2 to table4. This final table dt3 is your solution.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Steven
  • 289
  • 2
  • 5
  • 14