Retrieving a list of blog posts with related tags with less query

Question

Hi all, image this two tables:

Table: Item
Columns: ItemID, Title

Table: Tag
Columns: TagID, ItemID, Title

Which is the best way (without changing table structure (yes, I don't mind if they are not normalized)) to retrieve a list of items with all their tags attached using less possible query (i.e. not doing 11 queries to retrieve 10 items)?

Answer 1

Here's a simple outer join. Is that what you are after?

SELECT
  It.ItemID,
  It.Title [ItemTitle],
  Tg.TagID,
  Tg.Title [TagTitle]
FROM Item It
LEFT OUTER JOIN Tag Tg
ON It.ItemID = Tg.ItemID

Answer 2

I'm not entirely sure what you're after, but does this help?

select Item.ItemID, Item.Title, Tag.TagID, Tag.Title from Item, Tag 
  where Item.ItemID=Tag.ItemID

When you say you don't want to do 11 queries, do you mean you don't want to do 11 SQL queries, or you don't want to receive your results in 10 rows? If the latter, then I think it really just means you need to loop through the results in whatever language you're using to call SQL. E.g., in PHP:

$query = "select Item.ItemID as i, Item.Title as t1, Tag.TagID as t, Tag.Title as t2 from Item, Tag where Item.ItemID=Tag.ItemID";
$dataset = mysql_query($query) or die(mysql_error());

$items = Array();

while ($data = mysql_fetch_assoc($dataset))
{
  $items[$data['i']] = Array($data['t1'], $data['t'], $data['t2']);
}

Answer 3

What if I'd like to paginate the results? I cannot do a limit 0,10 in the query and get the first 10 items, or get the first 3 tags, or other things like that...