two DISTINCT() values needed in same query

[richrock]

Hi,

 

Been scratching my head over this for a while now.

 

I have two tables, one purchases, the other won items.  Both have invoice numbers attached to the items in each table, which may (or may not) be unique to each table... eg

 

purchasesinvoicenumwon itemsinvoicenum

Item 1INV-001Won Item 1INV-001

Item 2INV-002Won Item 2INV-003

 

And so on.

 

In order to find all distinct Invoices in both tables, I need to join the tables, and find all DISTINCT() invoice numbers.

 

I've never seen any LEFT JOIN query using more than one DISTINCT statement, so can it be done?  Or am I going completely the wrong way?

 

My thinking is (poor code follows):

 

SELECT DISTINCT(inv_num) as invoices FROM purchases LEFT JOIN won_items ON inv_num GROUP BY purchases.invoices ORDER BY invoices DESC

 

But this is wrong, so....  ???

 

TIA

[fenway]

DISTINCT is *NOT* a function.

[Barand]

I'd use UNION here

 

SELECT DISTINCT inv_num FROM `purchases`
UNION
SELECT DISTINCT inv_num FROM `won_items`
ORDER BY inv_num

[richrock]

Sounds logical and good - I'll give it a shot and let you know!