[thelist] SQL subqueries in mySQL 3.23.54

Joshua Olson joshua at waetech.com
Mon Mar 1 16:04:28 CST 2004


> -----Original Message-----
> From: Tab Alleman
> Sent: Monday, March 01, 2004 4:50 PM
>
> john at johnallsopp.co.uk wrote:
>
> > SELECT * FROM A, B, C where A.id = B.id and A.id = C.id and (select
> > count(*) from A, B where A.id = B.id) > (select count(*) from A, C
> > where A.id = C.id)
>
> Oooh, a puzzle!  Let's see,
>
> SELECT * FROM A
> INNER JOIN B ON A.id=B.id
> INNER JOIN C ON A.id=C.id
> HAVING COUNT(B.id) > COUNT(C.id)
>
>
> Does that do it?

Tab,

You can't have a HAVING clause without a GROUP BY clause... and you can't
have a SELECT * with a GROUP BY clause...  so, this won't work.

I wish I knew the answer, but off-hand I don't.  Sorry.  :-(  If this
question hasn't been solved by later tonight I'll take a concerted stab at
it, but my gut reaction is that this problem isn't solvable without temp
tables and without subselects.

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com/service_areas/
706.210.0168




More information about the thelist mailing list