Post reply

Help - Counting problem using INNER JOIN

  • November 25, 2004 at 8:19 am

    #63569

    SELECT COUNT(table1.ID1) AS counter, table2.ID2, table2.ID3 FROM

    table2 INNER JOIN table1

    ON

    table2.ID2 = table1.ID2

    AND

    table2.ID3 = table1.ID3

    GROUP BY table2.ID2, table2.ID3

    Obviously this only returns stuff that is exists in Table1. I need a way to do the same that will return zero where there's no entry in table one yet. Any idea?

  • November 25, 2004 at 9:04 am

    #531541

    Try right join

  • November 25, 2004 at 10:09 am

    #531548

    SELECT SUM(CASE table1.ID1 WHEN NULL THEN 1 ELSE 0 END) AS counter, table2.ID2, table2.ID3 FROM

    table2 LEFT JOIN table1

    ON

    table2.ID2 = table1.ID2

    AND

    table2.ID3 = table1.ID3

    GROUP BY table2.ID2, table2.ID3

    LinkedIn Profile

  • November 25, 2004 at 10:11 am

    #531549

    Thanks David, you steered me in the right direction - got it working correctly using 'LEFT JOIN'

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply