Let's take SO site. It has questions and questions have its tags. My question is, what is the best way to display questions and its tags. Let's say they are in diffirent tables.
SELECT * FROM questions RIGHT JOIN tags ON questions.id = tags.question_id
But there are more tags than one, so how would we display them together? Or we just have to make 2 SELECTs? Because if yes, then it would take lots of resources, because we display more than 1 question in one page.
Firstly SO requires at least one tag so the right join isn't needed, it should actually be a left outer join if you want to do what I think you do...
As you say, there can be more than one tag, which suggests that we need an or or in or returning multiple rows or doing some sort of string aggregation.
However, that wouldn't be a very good data model as you still need to know what tags that question has. So, the best way to do it would be to store the tags in the posts table, without bothering with the tag_id mess.
This is what SO actually does.
Your query (would actually work) would then be:
select Tags
from Posts
where Id = ?
Tags is a nvarchar(150), which is a separated list of the tags for that question, in the same format you see them on your question if that's of interest!
SO will automatically create a tag whenever a new one is entered and uses synonyms to ensure that people's mis-spellings get corrected. So, there's a Tags table Id | TagName and a TagSynonoyms table, ( I don't know where the wiki summaries are kept ). You can then at the assignment of a tag check to see whether it's got a synonym and correct it before writing to the Posts table.
To answer your question though, the query would be ( also would actually work ):
select p.*, t.TagName
from Posts p
join PostTags pt
on p.Id = pt.PostId
join Tags t
on pt.TagId = t.Id
I don't think that's what SO actually does - I don't even know why there is a PostTags or a Tags table as any tag is allowed there's no need to do the foreign key lookup to check whether a tag exists. And there's no need to store the relationship in this normalised way. You only need the column Tags in Posts and the TagSynonyms table to ensure that the synonyms system works. Then, if the primary key wasn't Id but TagName you only need one look-up.
Assuming tags table has a name column, I'd use GROUP_CONCAT function.
SELECT questions.*,
GROUP_CONCAT(DISTINCT tags.name -> ORDER BY tags.id SEPARATOR ',') AS `tag_names`,
GROUP_CONCAT(DISTINCT tags.id -> ORDER BY tags.id SEPARATOR ',') AS `tag_ids`
FROM questions
RIGHT JOIN tags
ON questions.id = tags.question_id
This will show 2 column tag_names and tag_ids where values will be comma separated. When we fetch the values We can explode it and get the real values.
while($row=mysql_fetch_assoc($result)){
$tag_names = explode("," $row['tag_names'];
$tag_ids = explode("," $row['tag_ids'];
$tags = array_combine($tag_ids, $tag_names);
}
Now $tags becomes a associated array where its key is the tag id and value is the tag name.
If you use following expression it'll create a JSON. But dont rely on it if your tag names have any characters which needs to be escaped (e.g. ")
Concat('{', GROUP_CONCAT(Concat('"', tags.id, '":"', tags.name, '"' )
ORDER BY
tags.id), '}') AS `tag_o`
Get JSON object by $tags = json_decode($row['tag_o']);
The below one gives you all the tags of a specific question.
SELECT * FROM `questions` q INNER JOIN `tags` t ON q.id = t.question_id;
This assumes that your tags table is a many-to-many between tag and questions.
SELECT questions.*, GROUP_CONCAT(CONCAT(tag.id, '::', tag.name))
FROM questions
LEFT JOIN tags
ON questions.id = tags.question_id
LEFT JOIN tag
ON tags.tag_id = tag.id
GROUP BY questions.id