Help with SQL Query

[Anon]

I have a question I'm hoping someone could help me with. Lets say I have two mysql tables each with a field named "city"

I want to run a query to pull out all of the distinct city names between both tables.
so far with select distinct table1.city, t able2.city from table1, table2 i am able to get:
city1, city1
city2, city1
city3, city1
etc...

I know the above query is incorrect, but what is the right way to query and get one column of distinct cities from each table and be able to sort by city?

thanks in advance

[Anon]

SELECT DISTINCT city FROM tablename1, tablename2

I don't know if my query will work... I've never tried something like that.

[Anon]

If you do it that way City is ambiguous

icknay wrote:

SELECT DISTINCT city FROM tablename1, tablename2

I don't know if my query will work... I've never tried something like that.

[mikeq]

what about

select distinct(CITY) from table1
UNION
select distinct(CITY) from table2

Cheers
Mike

[Anon]

Mike Quinn wrote:

what about

select distinct(CITY) from table1
UNION
select distinct(CITY) from table2

Cheers
Mike

Thanks for the reply, but from what I read MySQL doesn't support UNIONS, nor could i find anything about UNIONS in the MySQL doc's

any other ideas??

[mikeq]

Hi Max,

Sorry about that, I use Oracle a lot and forget what is missing from MySQL at times.

I found this in the docs under 3.5.7

For the moment you can solve this very efficiently by using a TEMPORARY table. This type of optimization is also very good if you are using very complicated queries where the SQL server does the optimizations in the wrong order.

CREATE TEMPORARY TABLE tmp
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1';
INSERT INTO tmp
SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';
SELECT * from tmp;
DROP TABLE tmp;

The above way to solve this query is in effect an UNION of two queries.

maybe a possibility?

Mike

[Anon]

Thanks for the help Mike.
A temp table could definately be an option. although I've tried working with temp tables in MySQL before, but had a lot of trouble with them. It just seems like creating a tmp table would be a little overkill. I am really looking for a simpler solution, but if there is no other...

anyone have any more ideas???

thanks

Mike Quinn wrote:

Hi Max,

Sorry about that, I use Oracle a lot and forget what is missing from MySQL at times.

I found this in the docs under 3.5.7

For the moment you can solve this very efficiently by using a TEMPORARY table. This type of optimization is also very good if you are using very complicated queries where the SQL server does the optimizations in the wrong order.

CREATE TEMPORARY TABLE tmp
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1';
INSERT INTO tmp
SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';
SELECT * from tmp;
DROP TABLE tmp;

The above way to solve this query is in effect an UNION of two queries.

maybe a possibility?

Mike