CODEWITHSUNDEEP
mysqlsql
daniel gon
daniel gon

Reputation: 159

Sql query returns duplicate

I have executions and each execution has several images and videos, example,

table execution

 id |    name
 1   execution1

table image

id |   executionId | image
1          1          'path'
2          1          'path2'

table audio

id |   executionId | audio
1          1          'path3'

I want a query that get all the executions, with each execution having all their images like,

  { executions: [
         { id: 1,
           name: execution1,
           images: 'path+path2',
           audio: 'path3'
          },
         {...}
      ]
 }

I have this query:

 query = 
    "select a.id, group_concat(image.image SEPARATOR '+'), group_concat(audio.audio SEPARATOR '+') 
    from execution a 
    left join image on image.executionId = a.id 
    left join audio on audio.executionId = a.id 
    group by a.id";

but returns

  { executions: [
         { id: 1,
           name: execution1,
           images: 'path+path2',
           audio: 'path3+path3'
          },
         {...}
      ]
    }

Why ?

Upvotes: 1

Views: 25

Answers (1)

GMB
GMB

Reputation: 222402

You have a 1-N (or maybe N-M) relationship in the data that is being aggregated. If there is one record in audio but two in image (or the other way aroud), you will get duplicates values in the string generated by GROUP_CONCAT(audio ...).

On solution is to use DISTINCT to avoid duplicating data in GROUP_CONCAT :

select 
    a.id,  
    group_concat(DISTINCT image.image SEPARATOR '+'),
    group_concat(DISTINCT audio.audio SEPARATOR '+') 
from execution a 
left join image on image.executionId = a.id 
left join audio on audio.executionId = a.id 
group by a.id

Upvotes: 1

Related Questions