CODEWITHSUNDEEP
mysqlinner-join
NateH06
NateH06

Reputation: 3584

Getting 2 working, independent inner joins between 3 tables to work as one query

For set up I have 3 tables:

TableA (date, name, balance)
TableB (date, name) 
TableC (date, name)

And to start with I am comparing B & C to A - one at a time, and have one working inner join when comparing two tables at once:

SELECT * FROM TableA A
INNER JOIN TableB B 
ON A.date = B.date AND A.name = B.name  ;

And, in another place, I do the same thing, except swap B with C. However, I want to be able to do both comparisons of B&C to A in one query. Something like: 

SELECT * FROM TableA A
INNER JOIN TableB B 
ON A.date = B.date AND A.name = B.name
INNER JOIN TableC C
ON A.date= C.date AND A.name = C. name  ;

But this returns nothing, I suspect it is comparing B to A, and then comparing the results of that to C. Am I correct in this?

However, here are the caveats. I don't want B & C comparing against each other at all. And, if lines 2 & 3 return nothing, I want 4 & 5 to act like the independent query, where it just compares A to C. Alternatively, if lines 4 & 5 return no results, I want 2&3 to act as normal.

If comparing A to B returns nothing, I know A & C will return something, or if comparing A to C returns nothing, A & B will return something. But I don't want the no results of one side corrupting the other.

Thank you!

Upvotes: 0

Views: 447

Answers (1)

letmutx
letmutx

Reputation: 1416

What you're looking for is a UNION.

SELECT * FROM TableA A
INNER JOIN TableB B 
ON A.date = B.date AND A.name = B.name

UNION

SELECT * FROM TableA A
INNER JOIN TableC C 
ON A.date = C.date AND A.name = C.name  ;

Or when you need to keep duplicates

SELECT * FROM TableA A
INNER JOIN TableB B 
ON A.date = B.date AND A.name = B.name

UNION ALL

SELECT * FROM TableA A
INNER JOIN TableC C 
ON A.date = C.date AND A.name = C.name  ;

Check out http://www.mysqltutorial.org/sql-union-mysql.aspx

Upvotes: 2

Related Questions