Selecting from multiple identical Tables

[perrohunter]

Hi, im have a database that stores data per week, in multiples tables that are identical

How can I select the data from multiple tables ?

example in both tables i ahve the serial and agen field so i tryed

SELECT serial, agen FROM week1,week2 WHERE agen='269'

I didnt work =P

[NogDog]

If the two tables are independent of each other and you simply want to list each serial/agen pair from both tables where agen has some value, then I think you are stuck with two queries (and asking yourself why they are separate tables).

If there is some relationship between the rows in the two tables that have the same value for the agen column, then you could do a join, but it's not clear to me whether that is applicable for this.

SELECT w1.serial, w1.agen, w2.serial, w2.agen
   FROM week1 w1 INNER JOIN week2 w2 ON w1.agen = w2.agen
   WHERE w1.agen = 269

[tomhath]

SELECT serial, agen
FROM week1
WHERE agen='269'
UNION ALL
SELECT serial, agen
FROM week2
WHERE agen='269'

But I predict 'agen' will have the value '269' in every row returned...:p

[Roger_Ramjet]

You should put all that weekly data in 1 table in the first place.