SQL USING Clause. If several columns have the same names but the datatypes do not match, the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an EQUIJOIN. USING Clause is used to match only one column when more than one column matches.
I'm trying to use the result of COUNT to limit the results. For example, I only want to show rows where the count (matchingimgcount) is greater than / equal to 2. SELECT telnumber.number, COUNT(DISTINCT(i2.idimg)) as matchingimgcountFROM imgreference iJOIN imgreference i2 ON i.idimg = i2.idimgAND i2.idtel=13777JOIN telnumber ON i.idtel = telnumber.idtelWHERE telnumber.idtel!= 13777GROUP BY i.idtel, i2.idtelI've tried two things: HAVING matchingimgcount = 2and WHERE matchingimgcount = 2No luck. Any suggestions would be greatly appreciated.
Use HAVING to filter based on values in the SELECT list: SELECT telnumber.number, COUNT(DISTINCT(i2.idimg)) as matchingimgcountFROM imgreference iJOIN imgreference i2 ON i.idimg = i2.idimgJOIN telnumber ON i.idtel = telnumber.idtelWHERE telnumber.idtel!= 13777AND i2.idtel=13777GROUP BY i.idtel, i2.idtelHAVING matchingimgcount 1Also, 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.
I've occasionally had a legitimate need for this kind of logic, and it usually involves multiple aggregates in the same SELECT, like 'count where FieldX is not null' and then 'count where FieldX is null' to compare the counts with each other, or 'count where FieldY = 2' and 'count where FieldY = 3' etc., again, to compare the counts. And I think that's what CodeKingPP was after. I liked this solution because it's what I usually do in these situations, and the comments between Canon and AndriyM are helpful to point out the potential misunderstanding or misuse.–Jun 20 '13 at 16:51.