MySql Inner join and exclude

Question

I have the following query. I need it to include all records from the 'yahrzeit' and tbldecedent table and the ones that are a match (dupes) should only be listed once.

SELECT *, Count(*)
FROM yahrzeit
WHERE confirmed = 1

INNER JOIN tbldecedent ON CONCAT( yahrzeit.firstName,  ' ', yahrzeit.middleName,  ' ', yahrzeit.lastName ) = tbldecedent.Name
AND DATE_FORMAT( CONCAT( yahrzeit.gregorianYear,  '-', yahrzeit.gregorianMonthNum,  '-', yahrzeit.gregorianDay ) ,  '%Y-%m-%d' ) = tbldecedent.EngDate

GROUP BY tbldecedent.Name, tbldecedent.EngDate

What is your question exactly? Do you get an error? Are you not getting the right results? — Seirddriezel, May 08 '17 at 16:07
With this query I'm only getting the records that match in both tables, but I would like the results to have all the records that don't match from both tables and only list the ones that match one time. Meaning - I want to get all the records from both tables but no dupes. — MG1, May 08 '17 at 16:08
So you want a full outer join which isn't supported in mySQL use a LEFT join on your table then UNION with a RIGHT join on the same table order. example: http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql — xQbert, May 08 '17 at 16:16

xQbert · Accepted Answer · 2017-05-08T18:25:33.080

Where clauses belong after the joins. Since we're using outer joins we have to make sure the where clause criteria for the matching records is moved to the join or the outer join is negated.

in mySQL to simulate a full outer join we simple do a left and right join with a union ALL (union does a distinct which removes the duplicates)

SELECT tbldecedent.Name, tbldecedent.EngDate, Count(*) as Cnt
FROM yahrzeit
LEFT JOIN tbldecedent 
  ON CONCAT( yahrzeit.firstName,  ' ', yahrzeit.middleName,  ' ', yahrzeit.lastName ) = tbldecedent.Name
 AND DATE_FORMAT( CONCAT( yahrzeit.gregorianYear,  '-', yahrzeit.gregorianMonthNum,  '-', yahrzeit.gregorianDay ) ,  '%Y-%m-%d' ) = tbldecedent.EngDate
WHERE yahrzeit.confirmed = 1
  AND tblDecedent.name is null -- add this to only show no matches.
GROUP BY tbldecedent.Name, tbldecedent.EngDate

UNION ALL

SELECT tbldecedent.Name, tbldecedent.EngDate, Count(*) as cnt
FROM yahrzeit
RIGHT JOIN tbldecedent 
   ON CONCAT( yahrzeit.firstName,  ' ', yahrzeit.middleName,  ' ', yahrzeit.lastName ) = tbldecedent.Name
  AND DATE_FORMAT( CONCAT( yahrzeit.gregorianYear,  '-', yahrzeit.gregorianMonthNum,  '-', yahrzeit.gregorianDay ) ,  '%Y-%m-%d' ) = tbldecedent.EngDate
  AND yhrzeit.confirmed = 1    
WHERE CONCAT( yahrzeit.firstName,  ' ', yahrzeit.middleName,  ' ', yahrzeit.lastName ) is null -- add this to only show no matches.
GROUP BY tbldecedent.Name, tbldecedent.EngDate

To better understand joins I recommend: https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ the venn diagram approach is a good one.

Lastly, I don't recommend a select *, count() with a group by containing only two fields. Select should only include the values in the group by plus aggregates or constants. Current version of MySQL wouldn't let you get away with this without changing a global setting, and other engines simply don't support this method. MySQL extends the group by to allow it; but the results can be unexpected for the columns not listed in the group by . More on that in the docs: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

I'm getting an internal error 500 when I try to run this. The tbldecedent table has 32667 records — MG1, May 08 '17 at 16:34
Do you get an error when you run each individually? (without the union?) what table is confirmed in? — xQbert, May 08 '17 at 17:41
Error 500 is too Generic to troubleshoot. I'd suggest running this against the db directly to see if the error is in the above or in the generation (I highly doubt its a driver problem) — xQbert, May 08 '17 at 17:50
Is there a way to run this and exclude the matching records? — MG1, May 08 '17 at 18:21
Yes we simply need to check the outer jointed table has a null value on a key. if one side of the join is not found then that is the record you're after. I've updated my answer to include a line showing this on the where clauses. — xQbert, May 08 '17 at 18:23
already had. see previous comment and where clause updates in both selects. — xQbert, May 08 '17 at 18:50

MySql Inner join and exclude

1 Answers1