MySQL tag selection

Question

I have a table with tags. It has column identifiers, tagTypeId and tagName. Each item can have many tags. For each element I want to select the first tags with tagTypeId 1, 2 and 3. I tried to add 3 almost identical left connections inside my query, which worked fine, but it was much slower (for example, 5 seconds with a little data in the tables)

it's something like

select i.*, tag1.name, tag2.name, tag3.name from items i
left join (select t.id, t.tagName as name from tags t where t.tagTypeId=1) tag1 on ...
left join (select t.id, t.tagName as name from tags t where t.tagTypeId=2) tag2 on ...
left join (select t.id, t.tagName as name from tags t where t.tagTypeId=3) tag3 on ...

How can I achieve this better in one connection?

Answer 1

if you do something like

select i.*, tag.name, tagTypeId
  from items i left join (select t.id, t.tagName as name 
                            from tags t where t.tagTypeId in (1, 2, 3)) on ...
order by i.itemid, tagTypeId

You will get a few lines per element, which are easily transferred to the program.

Answer 2

SELECT * FROM 
(SELECT id id1, tagTypeId tag1 FROM items WHERE tagTypeId = 1 LIMIT 1) X1,  
(SELECT id id2, tagTypeId tag2 FROM items WHERE tagTypeId = 2 LIMIT 1) X2, 
(SELECT id id3, tagTypeId tag3 FROM items WHERE tagTypeId = 3 LIMIT 1) X3

, , .

Answer 3

. "" 1..3 Max(), .

select i.ItemId, i.ItemName,
    MAX(CASE WHEN(t.tagTypeID = 1) THEN t.TagName ELSE NULL END) AS Tag1Name, 
    MAX(CASE WHEN(t.tagTypeID = 2) THEN t.TagName ELSE NULL END) AS Tag2Name, 
    MAX(CASE WHEN(t.tagTypeID = 3) THEN t.TagName ELSE NULL END) AS Tag3Name
from items i
    INNER JOIN tags t
    on t.ItemID = i.ItemID
where t.tagTypeId IN (1,2,3)
GROUP BY i.ItemID, i.ItemName

SqlFiddle here