Jump to content

Archived

This topic is now archived and is closed to further replies.

sploit

selecting distinct values from 2 tables

Recommended Posts

<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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Hmm thanks.

 

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

Share this post


Link to post
Share on other sites

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

 

That would probably strip out the duplicates.

Share this post


Link to post
Share on other sites

welp!

 

SQL-query :  

 

SELECT DISTINCT ITEM from rates, offers LIMIT 0, 30

 

MySQL said:  

 

 

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

Share this post


Link to post
Share on other sites

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\'

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.