homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Selecting items with another item in common

 1:06 am on Oct 26, 2008 (gmt 0)

I'm not really sure how to phrase what I am trying to do.

Heres the scenario. A MySQL database has a table with 2 columns, neither unique : a list of product IDs and a list of category IDs. Basically it's indicating additional categories in which products should appear, apart from their main cat.

So, say like this:

pID ----- pCat
a ------- 1
a ------- 2
a ------- 5
a ------- 6

b ------- 2
b ------- 3
b ------- 4
b ------- 5

c ------- 1
c ------- 2
c ------- 7
c ------- 8

What I want to do is this.

I want to extract a list of all categories associated with any products that are listed as being in a specified category, let's say pCat 1.

So for example in this case my list would include 1,2,5,6,7 and 8 - but not 3 or 4, since they are only associated with pID b, which is not in cat 1.

Does that make sense? Is it possible?



 3:01 am on Oct 26, 2008 (gmt 0)

Are you trying to retieve pID's that are in multiple pCat's while exluding PID's that in only 1 pCat. I'm assuming pCat is the main category and pID is a productid...Or is pCat the main categories and all it's subcategories in sequential order?


 9:50 am on Oct 26, 2008 (gmt 0)

I don't want to retrieve pIDs at all.

I want to retrieve catIDs depending on their relationship to pID.

This is how I am phrasing it to myself while trying to make a query of it:

"Get all catIDs that have a pID in common with this catID"


 10:37 am on Oct 26, 2008 (gmt 0)

1. Create [temporary] table new_table with fields CatID and Count
2. Insert into new_table (CatID, Count) select CatID, count(*) from prod_table Group by CatID
3. Delete from new_table where Count = '1'

I haven't tested this but this should get you all the CatID's that have a one-to-many relationship with pID


 11:56 am on Oct 26, 2008 (gmt 0)

Thanks, I see where you are coming from, but I don't think that's it.

"this should get you all the CatID's that have a one-to-many relationship with pID"

That's not what I am looking for though.

I know I haven't explained this well, but I won't have a pID to begin with in this scenario, I will only have a single CatID.

I need to take that single known CatID, get a list of pIDs it is related to, then extract all the other catIDs related to those, and only those, pIDs.

This table has many-to-many relationships and is one that I'm stuck with and can't change - but if I could figure this out it would be seriously life enhancing!


 3:21 pm on Oct 27, 2008 (gmt 0)

FROM YourTable

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved