Table Join? Lookup Table?

[Jeff_Hight]

I'm working on a application that needs to allow a user to reorder items they have previously purchased. I have 3 tables I need to work from (I think) The first table is the customer info that contains their ID number:

Customer1 | 8761
Customer2 | 5487
Customer3 | 2365

A second table that contains all of the available products and their ID

Product1 | 1001
Product2 | 1010
Product3 | 2001
Product4 | 2020
and so on...


And a third table that contains the info for which products any given customer has ordered previously
Customer1 for example has ordered Product1, Product2 and Product3 previously:

8761 | 1000
8761 | 1010
8761 | 2000

Where I'm pulling my hair out, is how do I get the product names from the products table after Customer1 logs in with his ID?

I can a list of the products he's ordered easy enough:
SELECT prodID
FROM orders
WHERE prodID = custID

And get 1000,1010,2000

Now I need to query the product table to get the product name that goes with each ID...

Can anyone shed some light on this one for me?

Thanks in advance,
jH

[Dan_Bracuk]

select yourfields
from customer c join customer_product cp on c.customer_id = cp.customer_id
join product p on p.product_id = cp.product_id

where etc

[MikerRoo]

Dan's example is ok but you need to prefix the columns you select like the attached.
(Also you need left join for customers that haven't ordered anything yet)
Finally, if you are using MS access, let us know since the syntax is non standard for that.

[Jeff_Hight]

Thanks for the replies gents.

As archaic as it is, this one does use Access...

[MikerRoo]

Ok, the attached has the same example rewritten for Access' bogus syntax.

Try to write your queries using Access' query designer. You won't be able to read them but they will work.