MYSQL/PHP: Concat returning to many fields on LEFT JOIN

Question

I had a SELECT query with a LEFT JOIN working as desired. I then added one more table via a smilar LEFT JOIN and now I am getting a wierd result. Basically, for a group_concat where I was getting one item for every record, I am getting eight records. I don't see why this is happening because the join to the new table is analagous to several other joins that do not have this problem (that I have omitted from the example for clarity).

Here is the query that is fine:

$sql = "SELECT t.*, 
group_concat(tf.todoid) as `tftodoid`, 
group_concat(tf.id) as `tfid`, 
group_concat(tf.filedescript) as `tffiledescript`, 
group_concat(tf.filename) as `tffilename`, 
group_concat(tf.founderid) as `tffounderid`, 
group_concat(tf.ext) as `tfext`, 
group_concat(tf.lasttouched) as `tilt`
FROM titles `t`
LEFT JOIN titlefiles `tf` 
ON (tf.todoid = t.id AND tf.founderid = '$userid') 
WHERE t.userid='$userid' 
GROUP BY t.id";

And here is the query with the extra join that is now spilling out the multiple copies of the items:

$sql = "SELECT t.*, 
group_concat(tf.todoid) as `tftodoid`, 
group_concat(tf.id) as `tfid`, 
group_concat(tf.filedescript) as `tffiledescript`, 
group_concat(tf.filename) as `tffilename`, 
group_concat(tf.founderid) as `tffounderid`, 
group_concat(tf.ext) as `tfext`, 
group_concat(tf.lasttouched) as `tilt`, 
group_concat(s.id) as `stepid`, 
group_concat(s.step) as `steps` 
FROM titles `t`
LEFT JOIN titlefiles `tf` 
ON (tf.titleid = t.id AND tf.founderid = '$userid') 
LEFT JOIN steps `s` 
ON s.titleid = t.id 
WHERE t.userid='$userid' 
GROUP BY t.id";

Here is an example of output in JSON showing the difference:

First query:

"tfid":"56,57,58,59,60,61,62,63,64,65,66,67,68,75,76,81"

Second query:

"tfid":"56,57,58,59,60,61,62,63,64,65,66,67,68,75,76,81,56,57,58,59,60,61,62,63,64,65,66,67,68,75,76,81,56,57,58,59,60,61,62,63,64,65,66,67,68,75,76,81,56,57,58,59,60,61,62,63,64,65,66,67,68,75,76,81,56,57,58,59,60,61,62,63,64,65,66,67,68,75,76,81,56,57,58,59,60,61,62,63,64,65,66,67,68,75,76,81,56,57,58,59,60,61,62,63,64,65,66,67,68,75,76,81,56,57,58,59,60,61,62,63,64,65,66,67,68,75,76,81,56,57,58,59,60,61,62,63,64,65,66,67,68,75,76,81",

I suspect the problem has something to do with the JOIN or with the Group By statements but I can't see how to fix.

How can I ensure that I get only one fileid per file as opposed to eight?

Norbert · Accepted Answer · 2017-07-02 18:05:12Z

1

Alter the line as follows:

group_concat(DISTINCT tf.id) as `tfid`,

This then only gets you the unique ids. If you want them ordered add:

group_concat(DISTINCT tf.id ORDER BY tf.id ASC) as `tfid`,

answered Jul 2, 2017 at 18:05

Norbert

6,1043 gold badges19 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user6631314 Over a year ago

Does this DISTINCT delete dupes as in if you have two with same number on purpose?

user6631314 Over a year ago

I found out it does delete. This would be a good solution except I need to preserve dupes. For example, there could be multiple file extensions of jpg.

user6631314 Over a year ago

It looks like a subquery may be needed as in this answer stackoverflow.com/questions/12464037/…

Collectives™ on Stack Overflow

MYSQL/PHP: Concat returning to many fields on LEFT JOIN

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related