I have two MySQL tables, states and trans:

states (200,000 entries) looks like: id (INT) - also the primary key energy (DOUBLE) [other stuff]

trans (14,000,000 entries) looks like: i (INT) - a foreign key referencing states.id j (INT) - a foreign key referencing states.id A (DOUBLE)

I'd like to search for all entries in trans with trans.A > 30. (say), and then return the energy entries from the (unique) states referenced by each matching entry. So I do it with two intermediate tables:

CREATE TABLE ij SELECT i,j FROM trans WHERE A>30.; CREATE TABLE temp SELECT DISTINCT i FROM ij UNION SELECT DISTINCT j FROM ij; SELECT energy from states,temp WHERE id=temp.i;

This seems to work, but is there any way to do it without the intermediate tables? When I tried to create the temp table with a single command straight from trans:

CREATE TABLE temp SELECT DISTINCT i FROM trans WHERE A>30. UNION SELECT DISTINCT j FROM trans WHERE A>30.;

it took a longer (presumably because it had to search the large trans table twice. I'm new to MySQL and I can't seem to find an equivalent problem and answer out there on the interwebs. Many thanks, Christian