CODEWITHSUNDEEP
mysqljoinsubquery
Ryan
Ryan

Reputation: 531

Subquery That Shows Records Only Where the JOIN Doesn't Work

I created a relatively simple query in MySQL to give me a JOIN on three tables based on where first names and last names matched. From there, I wanted to write another query that would then only show me the records that didn't get matched from the JOIN -- but I couldn't quite figure out how to do it. I'm assuming that it has to do with using a subquery involving something like NOT IN and my original query, but I couldn't get it to give me the results I wanted.

This is the work-around I tried to come up with that partially functioned properly:

SELECT *, 
  if(t2.first=t1.first AND t2.last=t1.last, "Match", "No Match") AS "t2 Match",
  if(t3.first=t1.first AND t3.last=t1.last, "Match", "No Match") AS "t3 Match"
FROM t1
LEFT JOIN t2 ON t2.first=t1.first AND t2.last=t1.last
LEFT JOIN t3 ON t3.first=t1.first AND t3.last=t1.last
WHERE if(t2.first=t1.first AND t2.last=t1.last, "Match", "No Match")="No Match"
   OR if(t3.first=t1.first AND t3.last=t1.last, "Match", "No Match")="No Match";

I feel like this is something that's fairly simple and straight-forward, but I'm not getting the correct results. Can anybody help?

Thanks!

Upvotes: 0

Views: 93

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

No match means that the t2 (or t3) columns are populated with Nulls in the results. So you can use IS NULL checks:

SELECT t1.*
FROM t1
  LEFT JOIN t2 ON t2.first = t1.first AND t2.last = t1.last
  LEFT JOIN t3 ON t3.first = t1.first AND t3.last = t1.last
WHERE t2.first IS NULL
   OR t3.first IS NULL ;

And you were right, you can also write the queries using NOT IN (warning: only if the joining columns are not nullable. Otherwise you may have unexpected results):

SELECT t1.*
FROM t1
WHERE (first, last) NOT IN
      ( SELECT first, last  FROM t2 )
   OR (first, last) NOT IN
      ( SELECT first, last  FROM t3 )
  ;

or using NOT EXISTS:

SELECT t1.*
FROM t1
WHERE NOT EXISTS 
      ( SELECT 1
        FROM t2
        WHERE t1.first = t2.first
          AND t1.last = t2.last
      )
   OR NOT EXISTS 
      ( SELECT 1
        FROM t3
        WHERE t1.first = t3.first
          AND t1.last = t3.last
      ) ;

Upvotes: 5

Related Questions