• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 719
  • Last Modified:

#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

0
gollem
Asked:
gollem
  • 2
  • 2
1 Solution
 
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
 
snoyes_jwCommented:
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
 
gollemAuthor Commented:
Thanks for the help it works now. :)
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now