selecting distinct values from 2 tables

[sploit]

<Sorry I had posted this in the PHP Help area earlier... :? >

 

I have two (MySQL) tables: RATES and OFFERS. Both tables have a field called ITEM.

 

I need to select all distinct ITEM values from both these tables.

 

For example:

 

RATES

-------

ITEM

1

2

3

4

5

 

OFFERS

---------

ITEM

3

5

7

8

9

 

Then the result should have:

1, 2, 3, 4, 5, 7, 8, 9

 

What query should I use?

[barbatruc]

If you use PEAR, you can use two queries and harvest the ids in a single array:

$ITEMS = array();

$sql = "SELECT ITEM FROM <first_table> WHERE ...";

$ITEMS += $db->getCol($sql);

$sql = "SELECT ITEM FROM <second_table> WHERE ...";

$ITEMS += $db->getCol($sql);

$ITEMS = array_unique($ITEMS);

You have an array with unique IDs from both tables.

 

JP.

[sploit]

Hmm thanks.

 

Isnt there a MySQL query that I can just run at the console/command-line to get the desired results?

[techiedude]

Did you try \"SELECT DISTINCT ITEM from <first_table>,<second_table> where...\"

 

That would probably strip out the duplicates.

[sploit]

welp!

 

SQL-query :  

 

SELECT DISTINCT ITEM from rates, offers LIMIT 0, 30

 

MySQL said:  

 

 

Column: \'ITEM\' in field list is ambiguous

[techiedude]

try this:

SELECT DISTINCT r.items from rates r, offers o where...

 

It\'s telling you it\'s ambiguous because it doesn\'t know which column you want from which table because they both have the ITEM column.

 

Look at the MYSQL documentation for \'alias\'