SQL : Join of inner join

Question

I am a beginner on SQL. I have 3 tables (A,B and C) and I want to extract only the part of A that is part of B or C.

Here is an image of what I want to extract (in red) :

I know that the intersection between A and C is obtain by :

A inner join C on #the keys

and between A and B :

A inner join B on #the keys

My question is : how to join/add those two inner join ?

Possible duplicate of [Joining three tables using MySQL](https://stackoverflow.com/questions/3709560/joining-three-tables-using-mysql) — B001ᛦ, Apr 30 '19 at 08:16
A join B on ... join C on ... will not take the intersection between A and C only — LCMa, Apr 30 '19 at 08:18
@LCMa . . . Sample data and desired results is much more meaningful than something theoretical. There are many ways to do this, depending on what you actually want to do. — Gordon Linoff, Apr 30 '19 at 11:06
That picture communicates nothing. What does it have to do with joins? [Re joins & Venn diagrams.](https://stackoverflow.com/a/55642928/3404097) The rest is also unclear. Please give a [mcve] for your actual problem. — philipxy, May 01 '19 at 05:04
@philipxy the picture clearly communicates that the OP wants only the data from A that has crossover with B and C. — Nicholas Gentile, Apr 23 '21 at 13:07
@NicholasGentile "crossover with" doesn't mean anything. Tables are bags but Venn & Euler diagrams work for sets & not bags, so it's not clear exactly what the image legend is. The diagram circle labels & "intersection between" suggest the circles enclose rows of tables, but in "A inner join C on #the keys" (which isn't clear) the "the keys" not being "all columns" suggests that it's not intersection but some other join & that the circles enclose not row values but key subrow values. The question is not clear. You are jumping to conclusions. — philipxy, Apr 23 '21 at 14:16
@NicholasGentile I already left a comment 2 years ago when the question was posted saying neither the image nor the post were clear, and I gave links explaining why. No response. Anyway the odds that the asker ever thought that sticking "#the keys" in an ON was clear are zero. I have not trashed anything, you are shooting the messenger. PS The asker self-answered, and that answer is also not clear about what it's proposing or how that answers whatever it takes the question to be. — philipxy, Apr 24 '21 at 02:09

score 1 · Answer 1 · answered Apr 30 '19 at 11:04

I would use exists:

select . . . 
from a
where exists (select 1 from b where b.? = a.?) or
      exists (select 1 from c where b.? = a.?);

If you want columns from all tables, then use left join and use a where clause:

select . . .
from a left join
     b
     on . . . left join
     c
     on . . .
where c.? is not null or b.? is not null;

score -1 · Answer 2 · answered Apr 30 '19 at 08:34

-1

I finally found how to do that ... And it is very easy and fast :

SELECT ... FROM A
INNER JOIN B ON ....

UNION

SELECT ... FROM A 
INNER JOIN C ON ....

answered Apr 30 '19 at 08:34

LCMa

445
3
13

score -2 · Answer 3 · answered Apr 23 '21 at 11:38

-2

You got it. BTW, your picture is not correct, I think. If you are looking for records which are part of A and part of B "OR" C, the circles B and C should be without intersection. You draw records which are part of A and part of B "AND" C instead. If you draw it correctly, you'll see your final union result clearly.

answered Apr 23 '21 at 11:38

Pavel

1

2

This doesn't answer the question. Also there's no reason to say the picture is not correct; assuming the tables are sets not bags, it's a reasonable interpretation of the English. Although the question isn't clear. Your "should be without intersection" is wrong for a Venn diagram. – philipxy Apr 23 '21 at 11:49

SQL : Join of inner join

3 Answers3