Selecting items with another item in common

Forum Moderators: open

Message Too Old, No Replies

Selecting items with another item in common

abbeyvet

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?

homeless

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?

abbeyvet

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"

homeless

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

abbeyvet

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!

LifeinAsia

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

Try:
SELECT DISTINCT pCat
FROM YourTable
WHERE pID IN (SELECT pID FROM YourTable WHERE pCat=1)