Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2004
    Posts
    12

    Question Unanswered: SQL many-to-many join

    Hi
    worlddom here. I need to do a join on some tables and I cant get it right. Thank you in advance for any help you can offer! I need to fetch the articleNames and imgNames where the category id = '1' . My main table is 'Articles' and I have made a link table to 'Categories' and a link table to 'Images' for a total of 5 tables. I thought the SQL would look like this but it doesnt work:

    SELECT Articles.artName, Images.imgName
    FROM Articles
    INNER JOIN Categories2Articles
    USING (articleId)
    INNER JOIN Categories
    USING (catId)
    WHERE catId = 1

    ..this didnt work at all and also I cant figure out how include the fetch to the linked imgNames either.

    Articles:
    articleId
    articleName

    Categories2Articles:
    articleId
    catId

    Categories:
    catId
    catName

    Images:
    imgId
    imgName

    Images2Articles:
    articleId
    imgId

    Best regards,
    worlddom

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your tables look fine

    i personally do not use USING in my joins but if it works, go ahead

    your problem is that article categories are unrelated and separate from article images

    you cannot therefore expect to bring back categories and images in a join, because you will get cross join effects

    you can only bring them back in one query if it's a union:
    Code:
    select articlename
         , 'category'     as type
         , catname        as name
      from articles a
    inner
      join categories2articles  c2a
        on a.articleid = c2a.articleid     
    inner
      join categories c
        on c2a.catid = c.catid    
    union all      
    select articlename
         , 'image'       
         , imgname      
      from articles a
    inner
      join Images2Articles  i2a
        on a.articleid = i2a.articleid     
    inner
      join images i
        on i2a.imgid = i.imgid     
    order
        by 1,2,3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    12
    r937,
    Thanks for the speedy reply.
    I see what you mean. In essence Im trying to fetch the articles and their
    associated images that are linked to a category like "tips" for example.
    Do you have any thoughts about a better way?
    Thanks in advance for any help you can offer.

    worlddom

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by w0rldd0m
    r937,
    Thanks for the speedy reply.
    I see what you mean. In essence Im trying to fetch the articles and their
    associated images that are linked to a category like "tips" for example.
    Do you have any thoughts about a better way?
    Thanks in advance for any help you can offer.

    worlddom

    You need to re-think your table structure and have less tables:

    Ex:
    Table Articles
    Art ID
    Art Name
    Cat ID

    Table Images
    Image Id
    Image Name
    Art ID

    Table Categories
    Cat ID
    Cat Name


    Something like this - this way you could select stuff from only 3 tables

    Code:
    select a.art_name, i.image_name
    from articles a, images i, categories c
    where a.art_id = i.art_id
    and a.cat_id = c.cat_id
    and c.cat_name = 'Your Category Name'
    Something like thus

  5. #5
    Join Date
    Jan 2004
    Posts
    12
    Originally posted by r937
    your tables look fine

    i personally do not use USING in my joins but if it works, go ahead

    your problem is that article categories are unrelated and separate from article images

    you cannot therefore expect to bring back categories and images in a join, because you will get cross join effects

    you can only bring them back in one query if it's a union:
    Code:
    select articlename
         , 'category'     as type
         , catname        as name
      from articles a
    inner
      join categories2articles  c2a
        on a.articleid = c2a.articleid     
    inner
      join categories c
        on c2a.catid = c.catid    
    union all      
    select articlename
         , 'image'       
         , imgname      
      from articles a
    inner
      join Images2Articles  i2a
        on a.articleid = i2a.articleid     
    inner
      join images i
        on i2a.imgid = i.imgid     
    order
        by 1,2,3

    Perhaps, if I create a new link table like this:

    Categories2Articles2Images
    catId
    articleId
    imgId

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ss659, no offence, but you need to re-think your table structure

    the two relationships are many-to-many, and your structure is strictly hierarchical -- an article can belong to only one category, and an image can belong to only one article

    w0rldd0m's structure is clearly correct

    w0rldd0m, i already suggested the better way: two separate queries, that, for convenience, you can merge with UNION ALL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2004
    Posts
    12
    Originally posted by r937
    ss659, no offence, but you need to re-think your table structure

    the two relationships are many-to-many, and your structure is strictly hierarchical -- an article can belong to only one category, and an image can belong to only one article

    w0rldd0m's structure is clearly correct

    w0rldd0m, i already suggested the better way: two separate queries, that, for convenience, you can merge with UNION ALL
    indeed you did. Thank you very much for your help!

    Best regards,
    worlddom

  8. #8
    Join Date
    Jan 2004
    Posts
    12
    Originally posted by r937
    ss659, no offence, but you need to re-think your table structure

    the two relationships are many-to-many, and your structure is strictly hierarchical -- an article can belong to only one category, and an image can belong to only one article

    w0rldd0m's structure is clearly correct

    w0rldd0m, i already suggested the better way: two separate queries, that, for convenience, you can merge with UNION ALL
    r937,

    Im studying your SQL and Im getting lost. Im not being lazy, Im just a bit too new I guess. I don't understand what these two bits are used for:

    in your first select
    , 'category' as type
    , catname as name

    and in your second select
    , 'image'
    , imgname


    Lastly, I cant figure out in your SQL how the value I will pass for catId will fetch the image that is associated with the articles that are returned.
    I clearly see how the first part works, with the exceptions above, but its the second part where Im lost.

    Thank you any insight you can offer

    Best regards,

    worldom

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    uh oh
    Lastly, I cant figure out in your SQL how the value I will pass for catId will fetch the image that is associated with the articles that are returned.
    if you want to select a particular category, and get all the articles related to that category, you cannot say "the" image with the articles, because each article can have several images, and several articles may share an image, so you need to be clear on what it is you want to return with your query
    Code:
    catid articleid articlename imgid image
      1      23     widgets 101   4   widgetA
      1      23     widgets 101   5   widgetB
      1      23     widgets 101   8   widgetC
      1      24     text article    null    null
      1      25     make things   2   thingie
      1      25     make things   4   widgetA
      1      26     needlepoint  24   needle
    could you please confirm that the relationships are, as suggested, many-to-many?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2004
    Posts
    12
    Originally posted by r937
    uh oh
    if you want to select a particular category, and get all the articles related to that category, you cannot say "the" image with the articles, because each article can have several images, and several articles may share an image, so you need to be clear on what it is you want to return with your query
    Code:
    catid articleid articlename imgid image
      1      23     widgets 101   4   widgetA
      1      23     widgets 101   5   widgetB
      1      23     widgets 101   8   widgetC
      1      24     text article    null    null
      1      25     make things   2   thingie
      1      25     make things   4   widgetA
      1      26     needlepoint  24   needle
    could you please confirm that the relationships are, as suggested, many-to-many?
    r937,

    Yes the relationships are many-to-many. Just as you explained many articles can share an image and many images can share an article. Same goes for categories with articles. I have thought that the link tables manage the associations. There will be a variety of instances where one article gets one image and one category. I need to mostly pass in a category id value and have that id-value pull articles that match it and the images that follow suit for those articles.

    // I apologize for belaboring your goodwill with this.

    This is what made me think of a link table for Cats2Articles2Images, but maybe this would be the tail wagging the dog.

    Best regards,
    worlddom

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    good

    do you see the sample output i just gave? that's a 5-way join, and that is quite manageable -- the output, i mean
    Code:
    catid=1 header
      article=23 widgets 101
        images: widgetA, widgetB, widgetC 
      article=24 text article
      article=25 make things
        images: thingie, widgetA
      article=26 needlepoint
        images: needle
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2004
    Posts
    12
    Originally posted by r937
    good

    do you see the sample output i just gave? that's a 5-way join, and that is quite manageable -- the output, i mean
    Code:
    catid=1 header
      article=23 widgets 101
        images: widgetA, widgetB, widgetC 
      article=24 text article
      article=25 make things
        images: thingie, widgetA
      article=26 needlepoint
        images: needle
    r937,

    Your results do make sense to me. Now I just need to understand your sql better, I guess. I just cant figure out the two items I mentioned from my previous post and more importantly how you are pointing to the images for the articles results in the query before UNION ALL.

    select articlename
    , 'category' as type //What purpose is this serving?
    , catname as name //What purpose is this serving?
    from articles a
    inner
    join categories2articles c2a
    on a.articleid = c2a.articleid
    inner
    join categories c
    on c2a.catid = c.catid
    union all
    select articlename
    , 'image' //What purpose is this serving?
    , imgname //What purpose is this serving?
    from articles a
    inner
    join Images2Articles i2a
    on a.articleid = i2a.articleid
    inner
    join images i
    on i2a.imgid = i.imgid
    order
    by 1,2,3


    Best regards,

    worlddom
    Last edited by w0rldd0m; 02-10-04 at 12:44.

  13. #13
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by r937
    ss659, no offence, but you need to re-think your table structure

    the two relationships are many-to-many, and your structure is strictly hierarchical -- an article can belong to only one category, and an image can belong to only one article

    w0rldd0m's structure is clearly correct

    w0rldd0m, i already suggested the better way: two separate queries, that, for convenience, you can merge with UNION ALL
    None taken - Ive been known to be wrong time to time.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks

    me too

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jan 2004
    Posts
    12
    Originally posted by r937
    good

    do you see the sample output i just gave? that's a 5-way join, and that is quite manageable -- the output, i mean
    Code:
    catid=1 header
      article=23 widgets 101
        images: widgetA, widgetB, widgetC 
      article=24 text article
      article=25 make things
        images: thingie, widgetA
      article=26 needlepoint
        images: needle
    I got the first statement to work but I cant get the statement that is unioned to work. I left out some of the fields and this may be the problem but if it is, then I dont know how those fields will help me. Here is my SQL:

    SELECT Articles.artName, Images.imgName
    FROM Articles a
    INNER JOIN Categories2Articles c2a ON a.articleId = c2a.articleId
    INNER JOIN Categories c ON c2a.catId = c.catId
    WHERE c.catId = 1
    UNION ALL
    SELECT Articles.artName, Images.imgName
    FROM Articles
    INNER JOIN Images2Articles i2a ON Articles.articleId = i2a.articleId
    INNER JOIN Images ON i2a.imgId = Images.imgId

Posting Permissions

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