Multi joining child tables.... I think...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multi joining child tables.... I think...

Author

Topic

deco10
Starting Member

28 Posts

Posted - 2010-07-07 : 09:35:21

What I need to do is take the label id(s) inputted by user and find all of the articles and have all the labels the user is searching for (this is simple with only one label inputted)

Any help?

table: labels
id, label_text

table: articles
id, title, body

table: label_articles
id, label_id, article_id

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-07 : 09:42:15

It is starting with user input.
It is ending with a select that reads from label_articles joining articles and labels.

But between starting and ending we need more information.
Do you have stored procedure and the input is coming via parameter or what do you mean regarding "inputted by user"?
Do you talk about multivalue parameter?
What datatypes are involved?

No, you're never too old to Yak'n'Roll if you're too young to die.

deco10
Starting Member

28 Posts

Posted - 2010-07-07 : 09:51:48

its for a web app.
I don't have any stored procs.

For now we can just say I'm looking for articles that have tag id 2 AND 3

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-07 : 10:03:35

select
a.id,
a.title,
a.body,
l.label_text
from label_articles la
join articles a on la.article_id = a.id
join label l onl.id = la.label_id
where la.article_id in (2,3)

No, you're never too old to Yak'n'Roll if you're too young to die.

deco10
Starting Member

28 Posts

Posted - 2010-07-07 : 10:12:07

I expect this will return articles with tag id 2 OR tag id 3
I'll give it a whirl though.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-07 : 10:17:00

I don't see a "tag id" so I don't know what you mean...

No, you're never too old to Yak'n'Roll if you're too young to die.

deco10
Starting Member

28 Posts

Posted - 2010-07-07 : 10:37:44

quote:
Originally posted by deco10
For now we can just say I'm looking for articles that have tag id 2 AND 3

An article has many tags through article_tags.
I need to find the articles that have tags with id 2 and 3

I'm not sure if I clarified that or just made more of a mess of it :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-07 : 10:52:40

Can you please give example data and wanted output?

No, you're never too old to Yak'n'Roll if you're too young to die.

deco10
Starting Member

28 Posts

Posted - 2010-07-07 : 11:02:59

** sorry for the confusion: when I said "tag" I meant "label"

select all articles that _have_ labels with id 1 AND 2
so I want to select articles that have label "work" and label "photos"

The table label_articles shows that the article "my article1" has labels "work" and "photos".

So I would like to be able to find an article based on labels. If I was only looking for one tag I could do it like this:
SELECT * FROM articles
JOIN article_labels ON article_labels.article_id = article_id
JOIN labels ON article_labels.label_id = label.id
WHERE label.id = 1

table: labels
id, label_text
1 , work
2 , photos
3 , school

table: articles
id, title, body
1 , "my article1", "texttext"
2 , "my article2", "texttext"
3 , "my article3", "texttext"

table: label_articles
id, label_id, article_id
1 , 1 , 1
2 , 2 , 1
3 , 3 , 2

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-07 : 11:12:29

This?
select
a.id,
a.title,
a.body,
l.label_text
from label_articles la
join articles a on la.article_id = a.id
join label l onl.id = la.label_id
where la.label_id=1
and exists (select * from label_articles la2 where la2.article_id = la.article_id and la2.label_id=2)

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources