CODEWITHSUNDEEP
phpmysql
Martin Ueding
Martin Ueding

Reputation: 8719

select all elements from table A ordered by the number of entries in table B linked to each item

I have a table with bookmarks, lz_link and a table which stores each click on the links called lz_hits. Then I would like to display all the bookmarks ordered by the number of clicks that they received in the last week.

This is my query so far:

SELECT count(hit_time) as cnt, descr, l_id, url
FROM lz_link
LEFT JOIN lz_hits ON hit_lz=l_id
WHERE link_aktiv=1
  && hit_time >= '.(time()-Options::$most_hits_hit_days*3600*24).'
GROUP BY hit_lz
ORDER BY cnt DESC
LIMIT '.Options::$boxes_count.';

It works, but I only get one bookmark that was visited zero times in the given period. Any other bookmarks that were not visited are not shown at all.

Why does this happen?

How can I get all bookmarks, even if they were not visited in the given period?

Upvotes: 0

Views: 99

Answers (2)

Mark Wilkins
Mark Wilkins

Reputation: 41252

The query as written is ambiguous. Typically when using GROUP BY, it would be an error to include non-aggregated fields in the SELECT list that are not included in the GROUP BY clause. MySQL apparently allows this, but the result is considered ambiguous. Not being completely sure which fields are in which tables, I can't be completely sure, but this query may give the desired results. I am guessing here that l_id is in lz_link and hit_lz is in lz_hits.

SELECT count(hit_lz) as cnt, descr, l_id, url
    FROM lz_link
       LEFT JOIN lz_hits ON hit_lz=l_id
          AND hit_time >= '.(time()-Options::$most_hits_hit_days*3600*24).'
    WHERE link_aktiv=1
    GROUP BY l_id, descr, url
    ORDER BY cnt DESC

Upvotes: 0

Galz
Galz

Reputation: 6842

Move any condition which applies to lz_hits from the WHERE clause to the ON clause. Sorry I can't post the query - I am on a mobile device.

EDIT: maybe this can help?

SELECT count(hit_time) as cnt, descr, l_id, url
FROM lz_link
LEFT JOIN lz_hits ON hit_lz=l_id 
   AND hit_time >= '.(time()-Options::$most_hits_hit_days*3600*24).'
WHERE link_aktiv=1
GROUP BY hit_lz
ORDER BY cnt DESC
LIMIT '.Options::$boxes_count.';

Assuming link_active and hit_lz are from table lz_link. If link_active isn't, just move it to the ON clause like I did with hit_time. As for hit_lz - grouping by a column from a left join doesn't make a lot of sense. Consider using another column for grouping.

Upvotes: 1

Related Questions