SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    May 2003
    Location
    SoCal
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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, Im 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. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Find items with categoryid x or y, and then group by itemid and limit the result to itemids having count = 2 ?

  3. #3
    SitePoint Member
    Join Date
    May 2003
    Location
    SoCal
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jofa
    Find items with categoryid x or y, and then group by itemid and limit the result to itemids having count = 2 ?
    Seems a bit complicated especially when it becomes x AND y AND z etc.

    Got this from a different forum... I think this is the way to go about it...

    SELECT DISTINCT i.*
    FROM items i, item_categories ic1, item_categories ic2
    WHERE ic1.item = i.item
    AND ic1.category=1
    AND ic2.item = i.item
    AND ic2.category=2;

  4. #4
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That other solution seems more complicated, especially in the "x AND y AND z etc" cases

    While, in my example, only thing you have to add is "or categoryid = z" and change the "having count thing" from 2 to 3

  5. #5
    SitePoint Member
    Join Date
    May 2003
    Location
    SoCal
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How would this be accomplished SQLwise?
    Does it not call for a nested query?

  6. #6
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could do:
    Code:
    SELECT *
      FROM items i
     INNER JOIN it_cat ic ON ic.item = i.item
     WHERE ic.category = 1
       AND EXISTS( SELECT *
     		 FROM it_cat ic
     		WHERE ic_item = i.item
     		  AND ic.category = 2 )
    Just keep adding exists queries.

  7. #7
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jonathan_Beni
    How would this be accomplished SQLwise?
    Does it not call for a nested query?
    T-Sql reference: HAVING

    Not sure which method is the most efficient - that's MattR's area of expertise

  8. #8
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah you can use having, too.

    Code:
    SELECT COUNT( * )
      FROM item_cat
     WHERE item = 123
       AND category IN( 1, 2 )
     GROUP BY item
    HAVING COUNT( * ) = 2

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by MattR
    Yeah you can use having, too.
    Code:
    SELECT COUNT( * )
      FROM item_cat
     WHERE item = 123
       AND category IN( 1, 2 )
     GROUP BY item
    HAVING COUNT( * ) = 2
    isn't that exactly what jofa said in the first place?

    that method is a lot easier to extend, too

    i'm not a DBA either, but i will bet a beer that the HAVING performs better too

    rudy
    http://r937.com

  10. #10
    SitePoint Member
    Join Date
    May 2003
    Location
    SoCal
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you guys...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •