CODEWITHSUNDEEP
mysqljoin
Wige
Wige

Reputation: 3918

MySQL join that shows members of table a that are empty in table b but not vice versa

I'm sure this has been asked before, and I apologize for asking it again, but I've done some searching probably for the wrong terms and just haven't been able to find the right approach.

I have two tables. Websites is a list of web sites (id | website), cdd is a list of users and the site they were referred from (userid | website | etc..). Not every site that referred users is a sponsor, and thus in the websites table. Also, not every site that is a sponsor has sent us users. I need a list of the number of users from each sponsor, including the 0s.

This is the query I have so far:

SELECT w.website, COUNT(*) AS num FROM websites w LEFT JOIN cdd c ON w.website = c.website WHERE c.submitted LIKE '05/26/11 %' GROUP BY w.website ORDER BY num DESC;

There are five sites in the website table, but one has not sent any users. That one does not show up in the queries.

Any thoughts?

Upvotes: 0

Views: 305

Answers (3)

Dan J
Dan J

Reputation: 16718

The LEFT JOIN is correct, but because you are specifying a WHERE clause against the table (cdd) on the right side of the join, you are filtering out websites that have no associated cdd record. You need to specify that criterion like so:

[...]
FROM websites w 
LEFT JOIN cdd c ON w.website = c.website 
WHERE c.submitted IS NULL OR c.submitted LIKE '05/26/11 %' 
[...]

which includes the websites that don't join to any cdd record, or

[...]
FROM websites w 
LEFT JOIN cdd c ON w.website = c.website AND c.submitted LIKE '05/26/11 %' 'Replaces WHERE clause 
[...]

which includes all websites, but only joins to cdds with the matching submitted date.

Note: To ensure that sites with no associated users return a count of 0, you may also need to COUNT() a column from cdd, rather than *...

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135878

Make the INNER JOIN a LEFT JOIN instead and I think you're good to go.

SELECT a.site, COUNT(b.ref_site) AS num 
    FROM table1 a 
        LEFT JOIN table2 b 
            ON a.site = b.ref_site 
    GROUP BY a.site;

Upvotes: 0

Denis de Bernardy
Denis de Bernardy

Reputation: 78561

I need a list of the number of users from each sponsor, including the 0s.

In that case you probably should be using a left join:

SELECT a.site, COUNT(b.ref_site) AS num FROM table1 a LEFT JOIN table2 b ON a.site = b.ref_site GROUP BY a.site;

Upvotes: 1

Related Questions