CODEWITHSUNDEEP
mysqlsql
user2980769
user2980769

Reputation: 141

Using Count value in WHERE clause

I'm trying to use the result of COUNT to limit the results. For example, I only want to show rows where the count (matching_img_count) is greater than / equal to 2.

SELECT tel_number.number, COUNT(DISTINCT(i2.id_img)) as matching_img_count
    FROM img_reference i
        JOIN img_reference i2 ON i.id_img = i2.id_img
            AND i2.id_tel=13777
        JOIN tel_number ON i.id_tel = tel_number.id_tel
WHERE tel_number.id_tel != 13777
GROUP BY i.id_tel, i2.id_tel

I've tried two things:

HAVING matching_img_count >= 2

and

WHERE matching_img_count >= 2

No luck... Any suggestions would be greatly appreciated.

Upvotes: 0

Views: 1504

Answers (2)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this: 

SELECT tel_number.number, COUNT(DISTINCT(i2.id_img)) as matching_img_count
    FROM img_reference i
        JOIN img_reference i2
             ON i.id_img = i2.id_img
        JOIN tel_number
             ON i.id_tel = tel_number.id_tel
WHERE tel_number.id_tel != 13777
       AND i2.id_tel=13777
GROUP BY i.id_tel, i2.id_tel
HAVING COUNT(DISTINCT(i2.id_img)) >= 2

Upvotes: 1

Barmar
Barmar

Reputation: 780851

Use HAVING to filter based on values in the SELECT list:

SELECT tel_number.number, COUNT(DISTINCT(i2.id_img)) as matching_img_count
FROM img_reference i
JOIN img_reference i2 ON i.id_img = i2.id_img
JOIN tel_number ON i.id_tel = tel_number.id_tel
WHERE tel_number.id_tel != 13777
    AND i2.id_tel=13777
GROUP BY i.id_tel, i2.id_tel
HAVING matching_img_count > 1

Also, when performing an INNER JOIN, the ON clause should only have conditions that relate two tables. All single-table conditions should be in the WHERE clause.

Upvotes: 0

Related Questions