0

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?

user6631314
  • 1,751
  • 1
  • 13
  • 44

1 Answers1

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`,
Norbert
  • 6,026
  • 3
  • 17
  • 40
  • Does this DISTINCT delete dupes as in if you have two with same number on purpose? – user6631314 Jul 02 '17 at 18:36
  • 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 Jul 02 '17 at 18:48
  • It looks like a subquery may be needed as in this answer https://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result/12464135#12464135 – user6631314 Jul 02 '17 at 20:06