CODEWITHSUNDEEP
mysqlsqljoinwhere-clausehaving
Garbit
Garbit

Reputation: 6046

SQL join with with where and having count() condition

I have 2 tables

Sleep_sessions [id, user_id, (some other values)]

Tones [id, sleep_sessions.id (FK), (some other values)]

I need to select 10 sleep_sessions where user_id = 55 and where each sleep_session record has at least 2 tone records associated with it.

I currently have the following;

SELECT `sleep_sessions`.*
FROM (`sleep_sessions`)
JOIN `tones` ON sleep_sessions.id = `tones`.`sleep_session_id`
WHERE `user_id` = 55
GROUP BY `sleep_sessions`.`id`
HAVING count(tones.id) > 4
ORDER BY `started` desc
LIMIT 10

However I've noticed that count(tone.id) is basically the entire of the tones table and not the current sleep_session being joined

Many thanks for your help,

Andy

Upvotes: 0

Views: 3590

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220952

I'm not sure what went wrong with your query. Maybe, try

HAVING count(*)

The following query might be a bit more readable (having can be a bit of a pain to understand):

SELECT *
FROM (`sleep_sessions`)
WHERE `user_id` = 55
AND (SELECT count(*) FROM `tones` 
     WHERE `sleep_sessions`.`id` = `tones`.`sleep_session_id`) > 4
ORDER BY `started` desc
LIMIT 10

The advantage of this is the fact that you won't mess up the wrong semantics you have created between your GROUP BY and ORDER BY clauses. Only MySQL would ever accept your original query. Here's some insight:

http://dev.mysql.com/doc/refman/5.6/en/group-by-hidden-columns.html

Upvotes: 1

Related Questions