1

I'm trying to do an outer join in MySQL to identify records not common to 3 tables. Here's my current effort http://sqlfiddle.com/#!2/02b92/30

I'm trying to retrieve the records not common to all three tables: 2,C,c (tbl2) 6,E,c (tbl3). Instead, I'm getting back the one common record: 1,A,c.

Thanks

SELECT *
FROM tbl1 A
LEFT OUTER JOIN tbl2 B
ON(
  A.a = B.a 
  AND A.b = B.b
  AND A.c = B.c
)
RIGHT OUTER JOIN tbl3 C
ON(
  B.a = C.a 
  AND B.b = C.b
  AND B.c = C.c
)
WHERE A.a is not null;
  • 1
    I think this question should stay here, not migrated to SO. – ypercubeᵀᴹ May 14 '14 at 8:27
3

If you're not dead set upon using OUTER JOIN a way to get the expected result is

SELECT a, b, c
FROM   (SELECT 'tbl1' name, a, b, c
        FROM   tbl1
        UNION ALL
        SELECT 'tbl2' name, a, b, c
        FROM   tbl2
        UNION ALL
        SELECT 'tbl3' name, a, b, c
        FROM   tbl2) d
GROUP BY a, b, c
HAVING COUNT(DISTINCT name) < 3

otherwise you need to use FULL OUTER JOIN a type of join not supported by MySQL, but it can be emulated with a composition like (Full Outer Join in MySQL)

SELECT * FROM tbl1
LEFT JOIN tbl2 ON t1.id = t2.id
UNION
SELECT * FROM tbl1
RIGHT JOIN tbl2 ON t1.id = t2.id
|improve this answer|||||
  • This first query works perfectly! However, I wasn't able to get the second one to work as I needed it. Awarded you the answer. – user2732180 May 16 '14 at 4:19
  • The second one is a template for the FULL OUTER JOIN not the full query, that one will be similar to the query of JChao – Serpiton May 16 '14 at 12:37
0

I know it is ugly but I believe it works.

SELECT *
FROM tbl1 
left OUTER JOIN tbl2 
ON(
  tbl1.a = tbl2.a 
  AND tbl1.b = tbl2.b
  AND tbl1.c = tbl2.c
)
left OUTER JOIN tbl3
ON(
  tbl2.a = tbl3.a 
  AND tbl2.b = tbl3.b
  AND tbl2.c = tbl3.c
)
WHERE tbl1.a is null or tbl2.a is null or tbl3.a is null
union
select *
from tbl1
right OUTER JOIN tbl2 
ON(
  tbl1.a = tbl2.a 
  AND tbl1.b = tbl2.b
  AND tbl1.c = tbl2.c
)
right OUTER JOIN tbl3
ON(
  tbl2.a = tbl3.a 
  AND tbl2.b = tbl3.b
  AND tbl2.c = tbl3.c
)
WHERE tbl1.a is null or tbl2.a is null or tbl3.a is null
union
select * from tbl1
right outer join tbl2
ON(
  tbl1.a = tbl2.a 
  AND tbl1.b = tbl2.b
  AND tbl1.c = tbl2.c
)
left OUTER JOIN tbl3
ON(
  tbl2.a = tbl3.a 
  AND tbl2.b = tbl3.b
  AND tbl2.c = tbl3.c
)
WHERE tbl1.a is null or tbl2.a is null or tbl3.a is null
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.