[thelist] SQL subqueries in mySQL 3.23.54

[Joshua Olson]

> -----Original Message-----
> From: Tab Alleman
> Sent: Tuesday, March 02, 2004 10:31 AM
>
> Yeah, but I mean, can you do this:
> (
> SELECT MAX(A.Col_1), ...MAX(A.Col_n), B.id, Count(*) AS Count_B
> FROM B
> INNER JOIN A ON B.id=A.id
> GROUP BY B.id
> UNION
> SELECT MAX(A.Col_1), ...MAX(A.Col_n), C.id, Count(*) AS Count_C
> FROM C
> INNER JOIN A ON C.id=A.id
> GROUP BY C.id
> )
> WHERE Count_B > Count_C
>
> I'm not sure if you can do a WHERE on an entire UNION.

Tab,

You can do something like that... it's basically equivalent to creating a
runtime view.  In MSSQL (and any other SQL-92 compliant DB [1]) you could do
the following, for example:

SELECT derived_table.*
FROM
(
  SELECT field1 AS f1, field2 * field3 AS f2
  FROM table1
  WHERE field1 = 'mouse'
) derived_table
WHERE derived_table.f2 = 15

It's essentially the same as creating a view (let's call it view_table1):

  SELECT field1 AS f1, field2 * field3 AS f2
  FROM table1
  WHERE field1 = 'mouse'

and then doing the following:

SELECT *
FROM view_table1
WHERE f2 = 15

MySQL doesn't support views, so I doubt this would work--but I'm no MySQL
guru.  Any MySQL guru's out there know if this SQL-92 syntax will work in
MySQL 3.x?

If this SQL-92 syntax works then it's conceivable that you could use this to
solve the original poster's problem.

[1] http://lists.evolt.org/archive/Week-of-Mon-20020415/109935.html

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