MySQL where condition in JOIN returns too many rows

Question

I am having trouble with a SQL query. I would like to join the following 2 queries that work indiviually into one query.

This query should return 0 which is what I expect

SELECT COUNT(*) FROM table3 INNER JOIN table1 ON
    table3.id = table1.id
WHERE table3.somefield = '0' AND table1.field1 = '1' AND table1.field2 = 'something') AS counts

This query should return 2 which is what i expect

SELECT COUNT(*) FROM table3 INNER JOIN table2 ON
    table3.id = table2.id
WHERE table3.somefield = '0' AND table2.field1 = '1' AND table2.field2 = 'something') AS counts

I've combined the queries and moved the conditions to the JOIN but that causes the query to count too many rows

SELECT COUNT(*) FROM table3 
    LEFT JOIN table1 ON
        table3.id = table1.id AND (table1.field1 = '1'  AND table1.field2 = 'something')
    LEFT JOIN table2 ON
         table3.id = table2.id AND (table2.field1 = '1' AND table2.field2 = 'something')
WHERE table3.somefield = '0'

What i expect is for the first part of the query, it should return 0, the second part should be 2 but what gets output is 148 and it's got to be to do with the AND condition in the JOIN.

What is going wrong here?

score 0 · Answer 1 · edited May 23 '17 at 11:43

Found my answer here

mysql left join returns unexpected amount of rows

SELECT
    (SELECT COUNT(*) FROM table3 INNER JOIN table1 ON
        table3.id = table1.id
    WHERE table3.somefield = '0' AND table1.field1 = '1' AND table1.field2 = 'something') +
    (SELECT COUNT(*) FROM table3 INNER JOIN table2 ON
        table3.id = table2.id
    WHERE table3.somefield = '0' AND table2.field1 = '1' AND table2.field2 = 'something') AS countA,


    (SELECT COUNT(*) FROM table3 INNER JOIN table1 ON
        table3.id = table1.id
    WHERE table3.somefield = '1' AND table1.field1 = '1' AND table1.field2 = 'something') +
    (SELECT COUNT(*) FROM table3 INNER JOIN table2 ON
        table3.id = table2.id
    WHERE table3.somefield = '1' AND table2.field1 = '1' AND table2.field2 = 'something') AS countB

score 0 · Accepted Answer · answered Jun 22 '15 at 16:06

What's going on is that your individual queries used INNER JOINs and your combined query uses LEFT JOINs.

You could fix it by using an INNER JOIN on table2 and a LEFT JOIN on table1:

SELECT * FROM table3 
    INNER JOIN table2 ON
        table3.id = table2.id and table2.field1 = '1' AND table2.field2 = 'something'      
    LEFT JOIN table1 ON
        table3.id = table1.id and table1.field1 = '1'  AND table1.field2 = 'something'
WHERE table3.somefield = '0';

But that requires that you know something about your data before the query runs (in other words, it requires that you already know that table2 has matches and table1 does not). This is not really a good idea.

It would be better to just get the SUM of the COUNTs:

SELECT SUM(c) as totalCount
FROM (
  SELECT COUNT(*) as c FROM table3 INNER JOIN table1 ON
    table3.id = table1.id
  WHERE table3.somefield = '0' AND table1.field1 = '1' AND table1.field2 = 'something'
  UNION ALL
  SELECT COUNT(*) as c FROM table3 INNER JOIN table2 ON
    table3.id = table2.id
  WHERE table3.somefield = '0' AND table2.field1 = '1' AND table2.field2 = 'something'
) bothCounts;

MySQL where condition in JOIN returns too many rows

2 Answers2