homepage Welcome to WebmasterWorld Guest from 72.44.48.122
register, login, search, subscribe, help, library, PubCon, announcements, recent posts, open posts,
Accredited PayPal World Seller

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library : Charter : Moderators: physics & whoisgregg

Databases Forum

    
Selecting items with another item in common
abbeyvet




msg:3773562		 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




msg:3773581		 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




msg:3773643		 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




msg:3773651		 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




msg:3773674		 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




msg:3774285		 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)
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.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
WebmasterWorld ® and PubCon ® are a Registered Trademarks of Pubcon Inc.
© Pubcon Inc. 1996-2012 all rights reserved