[thelist] SQL subqueries in mySQL 3.23.54

[Ken Schaefer]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Joshua Olson" <joshua at waetech.com>
Subject: RE: [thelist] SQL subqueries in mySQL 3.23.54


: > -----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.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

...or normalizing the data...

Cheers
Ken