Welcome to WebmasterWorld Guest from 54.158.4.182

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Selecting from three tables

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

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Aug 29, 2003
posts:944
votes: 0


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?

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

Preferred Member

10+ Year Member

joined:July 28, 2000
posts:580
votes: 0


select a.catname from table2 a, table3 b where a.catID=b.catID and b.prodID=<current product>
7:44 pm on Sept 30, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Aug 29, 2003
posts:944
votes: 0


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