How to prevent duplicate rows with a 1 to many SQL result without losing data

Question

I have a blog posts table and a blog post tags table. One post has many tags. I first did this with a call to posts then a loop and call the tags table, but this resulted in 9 queries per page just to get the posts. I have rewritten this to use a join, but it returns duplicate blog posts rows.

SELECT p.timestamp
     , p.title
     , p.postcontent
     , p.siteid
     , t.tag 
  FROM user_blogposts p
  LEFT 
  JOIN user_blogposttags t
    ON t.postid = p.id 
 WHERE p.userid = 1 
 LIMIT 8

This query returns the data I want, but if there are 5 tags per post, each post will be duplicated 5 times with only the tag field being different. I can't use GROUP BY as this will return 1 row with 1 tag for each post and break the one to many connection.

Is there a way to return an array for the many join so that I only get 1 row for the post and then the tag column is an array or comma delimited string for me to work with rather than having to filter out all of the duplicate data.

this is going to break the many relationship. i will get 1 row with 1 tag even if there are several — Dan Hastings, Jan 18 '16 at 12:42
I think this is what you're looking for: http://stackoverflow.com/questions/13451605/how-to-use-group-concat-in-a-concat-in-mysql — Jason Kennaly, Jan 18 '16 at 12:43
You can use sub query to get the tags. And use `group_concat()` to get the tags as comma separated — Sanchit, Jan 18 '16 at 12:44
@JasonKennaly this looks like it is for a single table. I could achieve the same comma delimited string by looping the results. It will still result in me having to make 9 queries to the DB — Dan Hastings, Jan 18 '16 at 12:46

score 1 · Accepted Answer · answered Jan 18 '16 at 12:48

1

This query should work for you:

SELECT user_blogposts.*, (SELECT GROUP_CONCAT(tag) FROM user_blogposttags WHERE user_blogposttags.postid=user_blogposts.id) AS tags
FROM user_blogposts

May need minor changes as per your table structure

answered Jan 18 '16 at 12:48

Sanchit

541
2
18

thank you, this is really helpful to know – Dan Hastings Jan 18 '16 at 13:04
Right..there is no limit to learning.. – Sanchit Jan 18 '16 at 13:21

score 1 · Answer 2 · answered Jan 18 '16 at 12:52

1

You can group and concat the tags like this:

SELECT p.timestamp, p.title, p.postcontent, p.siteid, 
 GROUP_CONCAT(t.tag SEPERATOR ",") AS tags
FROM user_blogposts p
LEFT JOIN user_blogposttags t
ON t.postid = p.id 
GROUP BY p.timestamp, p.title, p.postcontent, p.siteid;

answered Jan 18 '16 at 12:52

thanks for the response. There was another answer that used the same method posted first, but i gave an upvote for your answer as the GROUP_CONCAT solution worked perfectly. thank you – Dan Hastings Jan 18 '16 at 13:05

How to prevent duplicate rows with a 1 to many SQL result without losing data

2 Answers2