Jump to content


Photo

selecting distinct values from 2 tables


  • Please log in to reply
5 replies to this topic

#1 sploit

sploit
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 25 June 2003 - 01:31 PM

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

#2 barbatruc

barbatruc
  • Members
  • PipPip
  • Member
  • 28 posts
  • LocationMontreal, Quebec, Canada

Posted 25 June 2003 - 05:22 PM

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.
Unfortunately, PHP \'empowered every moron with a copy of Windows notepad to be \"web programmers\". (...) Give PHP a real INFRASTRUCTURE. Use PEAR!!!\'

#3 sploit

sploit
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 26 June 2003 - 05:32 AM

Hmm thanks.

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

#4 techiedude

techiedude
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 26 June 2003 - 07:57 PM

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

That would probably strip out the duplicates.

#5 sploit

sploit
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 30 June 2003 - 07:46 AM

welp!

SQL-query :  

SELECT DISTINCT ITEM from rates, offers LIMIT 0, 30

MySQL said:  


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



#6 techiedude

techiedude
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 30 June 2003 - 01:41 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users