#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

gollemAsked:
Who is Participating?
 
snoyes_jwConnect With a Mentor Commented:
Yes, I hadn't thought of that. You could alter your subquery to this:

SELECT t.recipient_id
FROM recipient r INNER JOIN recipient2targetgroup t USING (recipient_id)
GROUP BY t.recipient_id
HAVING count(t.recipient_id) > 1

This way, you allow MySQL to use any possible indexes to find matching recipient id's.  It's not necessary to group on both r.recipient_id and t.reciepient_id, since they are the same.
0
 
snoyes_jwCommented:
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...
0
 
gollemAuthor Commented:
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

0
 
gollemAuthor Commented:
Thanks for the help it works now. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.