CODEWITHSUNDEEP
mysqlsqljoin
Rocco The Taco
Rocco The Taco

Reputation: 3797

MySQL INNER JOIN returns zero results if all values are not found

I have the following query that works great when all of the INNER JOIN criteria is met. Unfortunately, this query returns zero results when for example INNER JOIN TableSample6 ON TableSample1.Field4 = TableSample6.Field6 is not found.

Can I restructure this query to return results even if some of the INNER JOIN values do not have a match in some cases?

SELECT DISTINCT
    TableSample1.Field4, 
    TableSample.Field2,
    TableSample2.Field2,
    TableSample3.Field3,
    TableSample4.Field4,
    TableSample5.Field1,
    TableSample.Field3,
    TableSample6.Field1,
    TableSample6.Field2,
    TableSample7.Field7,
    TableSample3.Field4,
    TableSample4.Field2,
    TableSample5.Field4
FROM TableSample1 
        INNER JOIN TableSample2 ON TableSample1.Field2 = TableSample2.Field4
        INNER JOIN TableSample ON TableSample1.Field6 = TableSample.Field4
        INNER JOIN TableSample3 ON TableSample1.Field3 = TableSample3.Field2        
        INNER JOIN TableSample4 ON TableSample1.Field7 = TableSample4.Field3
        INNER JOIN TableSample5 ON TableSample1.Field8 = TableSample5.Field2        
        INNER JOIN TableSample6 ON TableSample1.Field4 = TableSample6.Field6
WHERE (((TableSample1.Field4)="xxxxxx" AND (TableSample.Field2)="xxxxxx"))
    ORDER BY TableSample1.Field2;

Upvotes: 0

Views: 49

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522254

You could use LEFT JOIN:

SELECT DISTINCT
    t1.Field4, 
    t.Field2,
    t2.Field2,
    t3.Field3,
    t4.Field4,
    t5.Field1,
    t.Field3,
    t6.Field1,
    t6.Field2,
    t7.Field7,     -- PROBLEM: you never join to TableSample7
    t3.Field4,
    t4.Field2,
    t5.Field4
FROM TableSample1 t1
LEFT JOIN TableSample2 t2
    ON t1.Field2 = t2.Field4
LEFT JOIN TableSample t
    ON t1.Field6 = t.Field4
LEFT JOIN TableSample3 t3
    ON t1.Field3 = t3.Field2        
LEFT JOIN TableSample4 t4
    ON t1.Field7 = t4.Field3
LEFT JOIN TableSample5 t5
    ON t1.Field8 = t5.Field2        
LEFT JOIN TableSample6 t6
    ON t1.Field4 = t6.Field6
WHERE t1.Field4 = "xxxxxx" AND
      t.Field2 = "xxxxxx"
ORDER BY t1.Field2;

You could also use COALESCE() to handle records which do not match to all of the tables. For example, if TableSample2.Field2 were varchar, you could use COALESCE(t2.Field2, 'NA') to show NA instead of NULL.

Upvotes: 1

Related Questions