Selecting from three tables

[abbeyvet]

This is, broadly, the scenario.

Table 1: Associates a product with one category only
prodID, prodName, prodCat etc

Table 2: Stores info about Categories
catID, catName etc

Table 3: Associates products with additional categories and has only two fields
prodID, catID

Table 1 puts a product into its primary category, but Table 3 can associate that product with multiple other cats. So given product ids of, say, aaa, bbb and ccc, and category ids of 1, 2, 3, 4 etc, table three could look like this:

aaa 2
aaa 4
bbb 1
bbb 2
bbb 4
ccc 3
ccc 4

And so on.

What I want to do

For the current product

1. Get additional cats it is in from table three
2. Get the name(s) of those cats from table two
3. Print out the names of those cats

I am lost on quite how to construct the select for this. Anyone?

[mark_roach]

select a.catname from table2 a, table3 b where a.catID=b.catID and b.prodID=<current product>

[abbeyvet]

Thank you, that got me on track and it's done.