SQL many-to-many join

w0rldd0m · #1 (**permalink**) 02-10-04, 09:17

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

r937 · #2 (**permalink**) 02-10-04, 09:29

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

w0rldd0m · #3 (**permalink**) 02-10-04, 09:36

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

ss659 · #4 (**permalink**) 02-10-04, 09:42

Quote:

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

w0rldd0m · #5 (**permalink**) 02-10-04, 09:51

Quote:

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

r937 · #6 (**permalink**) 02-10-04, 09:56

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

w0rldd0m · #7 (**permalink**) 02-10-04, 09:59

Quote:

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

w0rldd0m · #8 (**permalink**) 02-10-04, 10:36

Quote:

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

r937 · #9 (**permalink**) 02-10-04, 11:01

uh oh

Quote:

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?

w0rldd0m · #10 (**permalink**) 02-10-04, 11:12

Quote:

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

r937 · #11 (**permalink**) 02-10-04, 11:27

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

w0rldd0m · #12 (**permalink**) 02-10-04, 11:42

Quote:

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

ss659 · #13 (**permalink**) 02-10-04, 13:42

Quote:

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.

r937 · #14 (**permalink**) 02-10-04, 16:35

thanks

me too

w0rldd0m · #15 (**permalink**) 02-10-04, 23:24

Quote:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech