|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
September 22nd, 2003, 03:45 AM
|
|||
|
|||
|
Generic "Many to Many" relationship question
Hi All!
I am sure that anyone new to databases has asked this question at one time, yet I still cannot find a decent answer… So, I’m trying here hoping someone can help… OK, lets say I want a database that would eventually become an online catalog. For the sake of simplicity, lets say the database initially has two tables: Items and Categories. ITEMS ItemID (pk) Item_name CATEGORIES CategoryID (pk) Category_name This is a many to many relationship: (Each Item can be in several Categories && Each Category can contain several Items) So, I define a composite table… ITM_CAT ItemID (pk) CategoryID (pk) SELECT * FROM itm_cat produces something like this: ItemID CategoryID 1 1 2 1 3 1 3 2 So now I can find all Items WHERE CategoryID = x Even WHERE CategoryID = x OR y But how on earth can I extract those items WHERE CategoryID = x AND y? Any assistance would be greatly appreciated.
|
|
#2
September 22nd, 2003, 06:28 AM
|
||||
|
||||
|
AND
Code:
select * from itm_cat
where CategoryID = 1
and CategoryID = 2
OR Code:
select * from itm_cat
where CategoryID = 1
OR CategoryID = 2
__________________
Left DevShed May 28, 2005. Reason: Unresponsive administrators. Free code: http://sol-biotech.com/code/. Secure Programming: http://sol-biotech.com/code/SecProgFAQ.html. Performance Programming: http://sol-biotech.com/code/PerformanceProgramming.html. It is not that old programmers are any smarter or code better, it is just that they have made the same stupid mistake so many times that it is second nature to fix it. --Me, I just made it up The reasonable man adapts himself to the world; the unreasonable one persists in trying to adapt the world to himself. Therefore, all progress depends on the unreasonable man. --George Bernard Shaw
|
|
#3
September 22nd, 2003, 08:44 AM
|
|||
|
|||
|
The and query
Code:
select itemid from itm_cat where CategoryID in (1,2) group by itemid having count(*) = 2 The one suggested in the previous post will never return any records.
|
|
#4
September 23rd, 2003, 02:26 AM
|
|||
|
|||
|
Thank you swampBoogie.
That works. Ummm, how would I modify that query to return the Item_name from Items instead of ItemID from itm_cat? Tried doing Code:
select item_name from items, itm_cat where CategoryID in (1,2) AND items.itemid = itm_cat.itemid group by itemid having count(*) = 2 but this returned no results... Any suggestions?
|
|
#5
September 23rd, 2003, 06:49 AM
|
||||
|
||||
|
Code:
select item_name
from items
inner
join itm_cat
on items.itemID = itm_cat.itemID
where itm_cat.CategoryID in (1,2)
group
by item_name
having count(*)=2
rudy http://r937.com/
|
|
#6
September 23rd, 2003, 11:07 AM
|
|||
|
|||
|
I eventually figured it out and did it with a LEFT JOIN like so:
Code:
SELECT item_name FROM Items LEFT JOIN itm_cat ON Items.itemid = itm_cat.itemid WHERE itm_cat.catid IN ( 1, 2 ) GROUP BY itm_cat.imgID HAVING COUNT( * ) = 2 It seems to work... Is there any significant difference between what was suggested in the previous post and this one?
|
|
#7
September 23rd, 2003, 12:45 PM
|
||||
|
||||
|
significant difference? depends on what you mean by "significant"
first of all, in any other database besides mysql, you must group by all non-aggregates in the SELECT list i.e. you cannot select item_name and then group by itm_cat.imgID secondly, LEFT OUTER doesn't make a difference, the query should produce the same results (bacuase the HAVING condition will throw out unmatched rows that the outer join might have returned), but performance-wise, it might be slower rudy
|
|
| Viewing: Dev Shed Forums > Databases > Database Management > Generic "Many to Many" relationship question |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
