asked on

#1054 - Unknown column ... in 'having clause'

Hi,

Can somebody see what's the problem with following SQL-statement. I've tested in Access and it worked but in MySQL it fails.

SELECT re.*
FROM recipient re,recipient2targetgroup r2t
WHERE re.recipient_id=r2t.recipient_id and r2t.targetgroup_id=26
and re.recipient_id in(

SELECT t.recipient_id
FROM recipient r, recipient2targetgroup t
GROUP BY r.recipient_id, t.recipient_id
having r.recipient_id = t.recipient_id and count(t.recipient_id) > 1 )

I get following error:
#1054 - Unknown column 'r.recipient_id' in 'having clause'

Thanks for the assistance.

Gollem

snoyes_jw

In versions prior to 5.0.2, the HAVING clause could only refer to columns present in the select list. In your subquery, you refer to r.recipient_id only the GROUP BY clause. Standard SQL dictates that your query should work, but MySQL did not follow this aspect of the standard until this late release.

To fix it, simply change your subquery to
SELECT r.recipient_id...

gollem

ASKER

Hi,

if I do this, won't I get the same error but than for this field?
=>t.recipient_id

...
and re.recipient_id in(

SELECT r.recipient_id
FROM recipient r, recipient2targetgroup t
GROUP BY r.recipient_id, t.recipient_id
having r.recipient_id = t.recipient_id and count(t.recipient_id) > 1 )

Thanks again for the help.

Gollem

ASKER CERTIFIED SOLUTION

snoyes_jw

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gollem

ASKER

Thanks for the help it works now. :)