Cross select querying three tables

This is just going a little over my head. I'm using a database
containing products, which, among other data, can have associated
tags, any number of them. Until now I used a serialize()'d PHP array
which I stored in a simple VARCHAR field with each product. Since I
want to be able to select products which have certain tags via SQL
though that'll need to change. My idea was to make a DB like this:

products [id, name, ...]
tags [id, name]
product_tag [product_id, tag_id]

The product_tag table would look like this:

product_id | tag_id
12 1
12 5
42 1
42 21
42 9
....

So any product can have any number of tags. Pretty basic db design as
far as I've heard. ;o)
I just can't figure out how to best SELECT my products now. I'm trying
this for example:

SELECT products.id, products.name, tags.id
FROM products INNER JOIN (tags, product_tag)
ON (product_tag.product_id = products.id AND product_tag.tag_id =
tags.id)
WHERE products.id = 12;

Which gives me two lines of results:

12 Product1 1
12 Product1 5

I would like a result like this though:

12 Product1 1,5

How can I summarise everything into a single result line with the tags
collapsed into a single field, something I can unserialize() or
explode() or something similar.

Thanks a lot, Cheers,
Dav

Re: Cross select querying three tables

David CZ wrote:
[...]
> SELECT products.id, products.name, tags.id
> FROM products INNER JOIN (tags, product_tag)
> ON (product_tag.product_id = products.id AND product_tag.tag_id =
> tags.id)
> WHERE products.id = 12;

The join with tags is not necessary.


> Which gives me two lines of results:
>
> 12 Product1 1
> 12 Product1 5
>
> I would like a result like this though:
>
> 12 Product1 1,5

Since you do not know how many tags a product has, this is
not possible (at least to my knowledge)

But that is not so much of a problem, you can do that in
your PHP code.

The only way I can think of, but I'd consider it a "dirty"
way, is the following:

* your tags are numbered 1, 2, 4, 8, ... This is possible
if you do not have too many tags, maybe maximum 32 or so
if you use 4-byte integers.

* Then, you can change the select to:

select p.id, p.name, sum(pt.tag_id) as tag_mask
from products p, product_tag pt
where p.id = pt.product_id
group by p.id, p.name

You will then have one tuple per product, with the
tags contained in the tag_mask.