SQL Server query on three tables using JOIN

Question

I am trying to learn how to use JOINs and for the life of me I cannot seem to get my head around the query I need to get the results I want.

My Tables:

What I am trying to achieve is:

Select all articles from Articles that are tagged as Gold and Silver

I believe my table relationships are OK, Green are PK's and Red FK's.

I have made multiple attempts at this and this is my latest:

SELECT Articles.Article_Name, Articles_Tagged.Tag_ID, Tags.Tag_Name
FROM Articles
INNER JOIN Articles_Tagged ON Articles.Article_ID=Articles_Tagged.Article_ID
WHERE Articles_Tagged.Tag_Name='Gold' AND Articles_Tagged.Tag_Name='Silver';

I believe my issue is the JOIN, which I think is throwing this message:

The multi-part identifier "Tags.Tag_Name" could not be bound.

You need to join to table `Tags` to use column from this table. — Rokuto, Jul 20 '17 at 08:07
add one more inner join to join your table Tags, `INNER JOIN Tags ON Tags.Tag_ID=Articles_Tagged.Tag_ID` — ProgrammingBaKa, Jul 20 '17 at 08:13
explains joins really well https://stackoverflow.com/questions/406294/left-join-vs-left-outer-join-in-sql-server — dbajtr, Jul 20 '17 at 08:14

score 3 · Accepted Answer · answered Jul 20 '17 at 08:15

3

A join will construct rows made up from each table you join on (including the one in the from clause, of course). Since no single row will have both "Gold" and "Silver" in it, your query will never return any rows.

One way to approach this problem is to count how many different "Gold" and "Silver" tags an article has, and return those with two:

SELECT *
FROM   articles a
WHERE  EXISTS (SELECT   at.article_id
               FROM     articles_tagged at
               JOIN     tags t ON at.tag_id = t.tag_id
               WHERE    at.article_id = a.article_id AND
                        tag_name IN ('Gold', 'Silver')
               GROUP BY at.article_id
               HAVING   COUNT(DISTINCT tag_name) = 2)

answered Jul 20 '17 at 08:15

Mureinik

297,002
52
306
350

Hi There, thank you for your answer. I am trying this out now. I am not sure I follow you fully. If you look at the Articles_Tagged table you can see that Article ID =1 is tagged Gold, Silver and Bronze. Wouldn't this mean that a row could have both Gold and Silver in there? – deucalion0 Jul 20 '17 at 08:31
I am getting the results I wanted from your answer, thank you very much for the help it is appreciated! – deucalion0 Jul 20 '17 at 09:14

score 1 · Answer 2 · answered Jul 20 '17 at 08:32

You can get the intersection of articles IDs which have gold and silver tag. Then get all the articles from these article id pool. I couldn't try it. There mey be some syntax errors in the following query. But it gives the main idea.

SELECT * FROM Articles WHERE Article_ID IN
  ((SELECT Articles_Tagged.Article_ID
       FROM Articles_Tagged
       WHERE Articles_Tagged.Article_Tagged_ID = 1)
  INTERSECT
   (SELECT Articles_Tagged.Article_ID
       FROM Articles_Tagged
       WHERE Articles_Tagged.Article_Tagged_ID = 2
   )
  )

score -1 · Answer 3 · answered Jul 20 '17 at 08:14

SELECT Articles.Article_Name, Articles_Tagged.Tag_ID, Tags.Tag_Name
FROM Articles
INNER JOIN Articles_Tagged ON 
    Articles.Article_ID = Articles_Tagged.Article_ID
INNER JOIN Tags ON  Articles_Tagged.Tag_ID = Tags.Tag_ID
WHERE Articles_Tagged.Tag_Name = 'Gold'
OR Articles_Tagged.Tag_Name = 'Silver';

You forgot to join Tags table and the condition was for Articles tagged as both Gold and Silver, use OR instead of And

score -2 · Answer 4 · edited Mar 31 '22 at 07:14

-2

This is how it should be

SELECT Articles.Article_Name, Articles_Tagged.Tag_ID, Tags.Tag_Name FROM Articles 
INNER JOIN Articles_Tagged ON Articles.Article_ID=Articles_Tagged.Article_ID
INNER JOIN TAGS ON TAGS.TAGID=Articles_Tagged.TAG_ID
WHERE TAGS.Tag_Name='Gold' AND TAGS.Tag_Name='Silver';

edited Mar 31 '22 at 07:14

RF1991

2,037
4
8
17

answered Jul 20 '17 at 08:21

Durgesh Sharma

3
5

SQL Server query on three tables using JOIN

4 Answers4