Database Normalization on LEFT JOIN?

Question

I wanted to load all my post tags with a single query into the MySQL server.

Now, I have decided to use LEFT JOIN because if there is no tags are associated with my post then also I will have that post, or you have any other suggestion for this?
Here is my query:

SELECT * FROM tags, tags_map 
LEFT JOIN posts on posts.cid = tags_map.pid 
WHERE tags.tag_id = tags_map.tid

Now the problem is duplicate data, for example if a post has three tags then the post itself will repeat three time!
In this case should I run another query for each post to have their tags? or can i get them all with a single query but without duplicate data?

Provide the DDL of the tables. – Abhik Chakraborty May 10 '15 at 16:58 — Abhik Chakraborty, May 10 '15 at 16:58
pls provide what result you get from your sql . – Pratik Joshi May 10 '15 at 17:01 — Pratik Joshi, May 10 '15 at 17:01

score 1 · Answer 1 · edited May 23 '17 at 11:43

1

I think you want group_concat

SELECT posts.*, 
   (select GROUP_CONCAT(tags.name)
    from  tags, 
      tags_map 
LEFT JOIN  on posts.cid = tags_map.pid 
WHERE tags.tag_id = tags_map.tid) as tagCombo
from posts

If you post a schema, sample data and expected results, ideally as a SQLFiddle, I can check.

edited May 23 '17 at 11:43

Community

1
1

answered May 10 '15 at 17:28

Neville Kuyt

29,247
1
37
52

Database Normalization on LEFT JOIN?

1 Answers1