How can I use MySQL to COUNT with a LEFT JOIN?

Question

How can I use MySQL to count with a LEFT JOIN?

I have two tables, sometimes the Ratings table does not have ratings for a photo so I thought LEFT JOIN is needed but I also have a COUNT statement..

Photos

id  name        src
1   car         bmw.jpg
2   bike        baracuda.jpg

Loves (picid is foreign key with photos id)

id  picid   ratersip
4   1       81.0.0.0
6   1       84.0.0.0
7   2       81.0.0.0

Here the user can only rate one image with their IP.

I want to combine the two tables in order of the highest rating. New table

Combined

id  name    src            picid
1   car     bmw.jpg        1
2   bike    baracuda.jpg   2

(bmw is highest rated)

My MySQL code:

SELECT * FROM photos 
LEFT JOIN ON photos.id=loves.picid 
ORDER BY COUNT (picid);

My PHP Code: (UPDATED AND ADDED - Working Example...)

$sqlcount = "SELECT p . *
FROM `pics` p
LEFT JOIN (

SELECT `loves`.`picid`, count( 1 ) AS piccount
FROM `loves`
GROUP BY `loves`.`picid`
)l ON p.`id` = l.`picid`
ORDER BY coalesce( l.piccount, 0 ) DESC";

$pics = mysql_query($sqlcount);

@TheBlackBenzKid please do **not** add your answer to the question.... Either create a new answer and refer to it in a comment, or don't do it at all... — Naftali, Jan 11 '12 at 17:03

score 6 · Accepted Answer · answered Jan 11 '12 at 16:39

6

MySQL allows you to group by just the id column:

select
    p.*
from
    photos p 
    left join loves l on
        p.id = l.picid
group by
    p.id
order by
    count(l.picid)

That being said, I know MySQL is really bad at group by, so you can try putting the loves count in a subquery in your join to optimize it:

select
    p.*
from
    photos p
    left join (select picid, count(1) as piccount from loves group by picid) l on
        p.id = l.picid
order by
    coalesce(l.piccount, 0)

I don't have a MySQL instance to test out which is faster, so test them both.

answered Jan 11 '12 at 16:39

Eric

92,005
12
114
115

The second query worked faster for me. and I added DESC after the last bracket so I could order them by highest to lowest. Thanks. Only issue is no converting for PHP – TheBlackBenzKid Jan 11 '12 at 16:50
Edited, I fixed it. Answer above. – TheBlackBenzKid Jan 11 '12 at 16:59

score 2 · Answer 2 · answered Jan 11 '12 at 16:38

2

You need to use subqueries:

SELECT id, name, src FROM (
  SELECT photos.id, photos.name, photos.src, count(*) as the_count 
  FROM photos 
  LEFT JOIN ON photos.id=loves.picid 
  GROUP BY photos.id
) t 
ORDER BY the_count

answered Jan 11 '12 at 16:38

Naftali

144,921
39
244
303

score 2 · Answer 3 · answered Jan 11 '12 at 16:41

2

select
      p.ID,
      p.name,
      p.src,
      PreSum.LoveCount
   from
      Photos p
         left join ( select L.picid,
                            count(*) as LoveCount
                        from
                           Loves L
                        group by
                           L.PicID ) PreSum
           on p.id = PreSum.PicID
   order by
      PreSum.LoveCount DESC

answered Jan 11 '12 at 16:41

DRapp

47,638
12
72
142

I like the code and indentation on this. So I gave a plus point. Easy to read. – TheBlackBenzKid Jan 11 '12 at 16:51
@TheBlackBenzKid, appreciate it... From the years of writing SQL, so many have such a blur, that it makes it hard for following... The primary "select, from, where, group by, order by, having" are at one level keeping all fields and/or criteria easier to identify. – DRapp Jan 11 '12 at 16:53

score 0 · Answer 4 · answered Jan 11 '12 at 16:43

I believe you just need to join the data and do a count(*) in your select. Make sure you specify which table you want to use for ambigous columns. Also, don't forget to use a group by function when you do a count(*). Here is an example query that I run on MS SQL.

Select CmsAgentInfo.LOGID, LOGNAME, hCmsAgent.SOURCEID, count(*) as COUNT from hCmsAgent
LEFT JOIN CmsAgentInfo on hCmsAgent.logid=CmsAgentInfo.logid
where SPLIT = '990'
GROUP BY CmsAgentInfo.LOGID, LOGNAME, hCmsAgent.SOURCEID

The example results form this will be something like this.

77615   SMITH, JANE 1   36
29422   DOE, JOHN   1   648

Hope that helps. Good Luck.

How can I use MySQL to COUNT with a LEFT JOIN?

4 Answers4