select columns from temp tables 1

[rps604]

Hello,

I have limited access and control of the data I am working with. The data comes from a data warehouse. relevant queries results are stored in temp tables. The number of records stored in each table is the same. A simplified example would be;

CREATE TABLE IF NOT EXISTS `t1` (
`index` int(5) DEFAULT NULL,
`data` varchar(10) DEFAULT NULL,
`t1_codex` int(6) DEFAULT NULL,
`other` int(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

simplifed data;


INSERT INTO `t1` (`index`, `build`, `t1_codex`, `other`) VALUES
(1, 'xe4510554xx39001', 12, 0),
(2, 'xf4510554xx40002', 45, 3),
(3, 'xf4510554xx41055', 14, 4),
(4, 'xf4510554xx42005', 335, 3);

There can be many of these temp tables created. More than 20 in some instances.

I need to select all the data in a single column from each of these tables. Using the above example

t1.t1_codex
t2.t2_codex
t3.t3_codex
etc...

and produce a result set such as;

t1_codex t2_codex t3_codex
12 77 04
45 96 89
14 05 66
435 44 01

etc...

BUT, problems.
any select I do produces a Cartesian product...

SELECT `t1`.`t1_codex` , `t2`.`t2_codex` , `t3`.`t3_codex`
FROM `t3` , `t2` , `t1`
ORDER BY `t1`.`t1_codex` ASC , `t2`.`t2_codex` ASC , `t3`.`t3_codex` ASC
LIMIT 0 , 30

I tried selecting the relevant data into a new table, no joy... I have been looking a this too long, I am sure there is a simple solution to this but i can't see it at the moment and need someone elses input. thanks in advance.

 

[guelphdad]

use a UNION ALL

 

[ingresman]

You're getting the cart prod because you don't have a where clause, so the optimizer will try and join every table row to every table row. so if you had two tables with 1000 rows in each you would end up with a result set of 1000 x 1000 = 1,000,000 rows, adding in a 3 1000 would get you to 1,000,000,000 which is a fair result set !.
I think what guelphdad says will possibly work or you could issue a list of union one for each table. It should work well as all your tables have the same structure.

http://dev.mysql.com/doc/refman/5.0/en/union.html

should show you the way.
The crosstab looks a little more interesting and I'm sure someome will come alomg with some SQL to do it, me I'd be reaching for my PHP pencil !