Get item and all related tags with one query

Question

I have the following tables structure:

Item:

Id,         
Description 

Tags:

Id,
TagName

ItemXrefTag

 Id,
 TagId,
 ItemId

What is the best way to read all the items related to some tag and all other tags, related to the items, to be able to display list of items and all the tags related to the item?

If it's not clear I`ll give the example:

Item, Tags:

 Code complete, book|programming|cool
 Reactoring, book|programming|refactoring|cool
 C# for dummies, book|dont like it| not cool

P.S. I'm using subsonic, but as it support getting data fro mthe query Im ok with the query that will let me get all the data. Sure I can perform join and iterate through multiple lines Ill get and make collection of items with collection of tags for each of it. Im just interested in the most efficient way to implement this.

Answer 1

here's an example to join all the data. unless you specify a format you want this is the best i can do.

declare @item table (IID int identity(1,1), Description varchar(max))
declare @tags table (TID int identity(1,1), TagName varchar(50))
declare @ItemXrefTag table (XID int identity(1,1), TID int, IID int)


insert into @item values ('Book A')
insert into @item values ('Book B')


insert into @tags values ('Awesome!')
insert into @tags values ('Suckage!')
insert into @tags values ('Mediocre')


insert into @ItemXrefTag values (1,1)
insert into @ItemXrefTag values (3,1)
insert into @ItemXrefTag values (2,2)








select *
from @ItemXrefTag a
    left outer join @tags b
        on a.TID=b.TID
    left outer join @item c
        on a.IID=c.IID

Answer 2

You are searching for the GROUP_CONCAT function. Just tried it on my database with

SELECT o.orderno,
       GROUP_CONCAT(d.itemno ORDER BY d.itemno ASC SEPARATOR ', ') as items
FROM order o
LEFT JOIN order_detail d ON o.id = d.order_id
GROUP BY d.order_id
ORDER BY o.id ASC

returns a result of order numbers with a comma seperated list of ordered items:

orderno   | items
----------------------------------
201010001 | 100123, 100456, 100987
201010002 | 123456, 123457

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

But I suppose this is not ANSI SQL so it won't be available for SQL-Server ;-), but a quick search here at stackoverflow returned this question: http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005

The corresponding command in sql server should be CROSS APPLY

Another good article about CROSS APPLY

http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

For usage with subsonic you should use an InlineQuery (aka CodingHorror) to execute raw sql with subsonic.