Rich Allen <[EMAIL PROTECTED]> wrote on 03/03/2005 04:28:07 PM: > iH > > i have a database that i only have READ access to (i am not the > creator). there are tables with stats data that are created each day; > ie "data3_1_2005" and 'data3_2_2005". each table has the same layout. > > create table data3_1_2005 ( > port char(8), > tmval int, > val int > ) > > how can i create a single query to get all the rows related to port > from both files without creating a merge table? > > > thanks > - hcir >
That's a UNION query... SELECT * FROM data3_1_2005 UNION SELECT * FROM data3_2_2005; That will dump both tables into one long list. If you need only certain rows, make that a condition for each select like this... SELECT * FROM data3_1_2005 WHERE port = 'ABCDE'; UNION SELECT * FROM data3_2_2005 WHERE port = 'ABCDE'; Make sense? Many read-only users could still have rights to create temporary tables. Have you tried this... CREATE TEMPORARY TABLE tmpPorts SELECT * FROM data3_1_2005 UNION SELECT * FROM data3_2_2005; Then you could use tmpPorts just like a MERGE table. You can either ":DROP TABLE tmpPorts;" to make it go away or it will be dropped for you when your connection disconnects. Personally, I prefer to do the first so that I don't leave a mess behind when I leave a server. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
