Welcome to WebmasterWorld Guest from 54.167.85.221

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Selecting from three tables

     

abbeyvet

10:47 am on Sep 30, 2008 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

11:28 am on Sep 30, 2008 (gmt 0)

10+ Year Member



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

abbeyvet

7:44 pm on Sep 30, 2008 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

Featured Threads

Hot Threads This Week

Hot Threads This Month