CODEWITHSUNDEEP
mysqlsql
Tomás Gray
Tomás Gray

Reputation: 59

SELECT association of tags and items

I'm playing around with a tags DB, and want to know how to build a query to get a list of tags, marking each one that is applied to a given item, like this:

Table 1
item name   | item ID
---------------------
One         | 1
Two         | 2
Three       | 3

Table 2
tag name    | tag ID
--------------------
Tag1        | 1
Tag2        | 2
Tag3        | 3

Mapper
tag ID      | item ID   | map ID
--------------------------------
1           |  1        | 1
1           |  3        | 2
3           |  1        | 3

Tags for item One
tag name        |  is_tagged
-------------------------------
Tag1            | *
Tag2            |
Tag3            | *

How can I do this?

Upvotes: 0

Views: 65

Answers (2)

Alexander
Alexander

Reputation: 4537

You could make left or right join of Table 2 and Table 1 through Mapper table, then test columns of Table 2 for NULL values. For example:

SELECT 
   TagName, ItemID IS NOT NULL AS IsTagged
FROM 
   Table2 
   LEFT JOIN 
   (SELECT TagID, ItemID 
   FROM Mapper INNER JOIN Table1 
   WHERE ItemName = 'One') AS tmpQry
   USING(TagID);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Here is one simple method:

select t.tagname,
       (case when exists (select 1
                          from mapper m join
                               items i
                               on m.item_id = i.item_id
                          where i.item_name = 'One' and
                                m.tag_id = t.tag_id
                         )
             then '*' else ''
        end) as is_tagged
from tags t;

You can also write this using left joins:

select t.tagname,
       (case when i.item_id is not null then '*' else ''
        end) as is_tagged
from tags t left join
     mapper m
     on m.tag_id = t.tag_id left join
     items i
     on m.item_id = i.item_id and i.item_name = 'One'

Upvotes: 2

Related Questions