[thelist] Tricky SQL

Jay Blanchard jay.blanchard at niicommunications.com
Tue Jun 18 14:20:00 CDT 2002


[snip]
I've got multiple tables in the same database that are identical in
structure, but not in data. (this is a sports database, so the "men's
soccer" table is identical to the "women's soccer" table, yet they have
different data in the rows).

What I want to do is use a wildcard for a table name. Something like:
"SELECT score FROM * WHERE gameDate = yesterday" to get all scores for all
games played by all teams in all sports for yesterday.

Any solutions to this?
[/snip]

You can't really do this. Even if you did SELECT a.score, b.score FROM tblA
a, tblB b you would get too many rows back as you would get tblA x tblB
rows. There is now join available. You would just have to write seperate
queries for each if you wanted to accurate data back without putting them
all in one table. There may be a solution though. By using a date table you
could do something like this

SELECT c.date, a.score, b.score
FROM tblC c LEFT OUTER JOIN tblA a
ON (c.date = a.date)
LEFT OUTER JOIN tblB b
ON (a.date = b.date)
WHERE c.date = yesterday's date

You will get rows with a date, a score or NULL from tblA, and a score or
NULL from tblB. Does that help?

Jay





More information about the thelist mailing list