SQLite group_concat returns many instances of the same data, can't use DISTINCT-CodePudding

I'm trying to return data from 4 tables using JOINs and group_concat

This is my current query:

SELECT  m.ID AS num
    , m.Name AS MOVIE
    , group_concat(v.Codec, '-') AS v_CODEC
    , group_concat(a.Codec, '-') AS a_CODEC
    , group_concat(s.Language, '-') AS SUBs
FROM File AS m
LEFT JOIN Video AS v ON m.ID = v.FileID
LEFT JOIN Audio AS a ON m.ID = a.FileID
LEFT JOIN Subtitle AS s ON m.ID = s.FileID
GROUP BY m.ID
ORDER BY m.ID

With my current query, my results look like this:

 ----- ---------- ---------------- ------------- ---------- 
| num |   MOVIE  |     v_CODEC    |   a_CODEC   |   SUBs   |
 ----- ---------- ---------------- ------------- ---------- 
| 1   | Superman | H264-H264-H264 | DTS-DTS-DTS | en-en-en |
|     |          | H264-H264-H264 | DTS-DTS-DTS | en-en-en |
 ----- ---------- ---------------- ------------- ---------- 
| 2   | Batman   | MPEG2-MPEG2    | MP3-MP3     |          |
 ----- ---------- ---------------- ------------- ----------

I would like my results look like this:

 ----- ---------- --------- ------------- ------- 
| num |   MOVIE  | v_CODEC |   a_CODEC   |  SUBs |
 ----- ---------- --------- ------------- ------- 
| 1   | Superman | H264    | DTS-DTS-DTS | en-en |
 ----- ---------- --------- ------------- ------- 
| 2   | Batman   | MPEG2   | MP3-MP3     |       |
 ----- ---------- --------- ------------- -------

This is my database estructure:

File
 ---- ---------- 
| ID |   Name   |
 ---- ---------- 
| 1  | Superman |
 ---- ---------- 
| 2  | Batman   |
 ---- ---------- 

Video
 -------- ------- 
| FileID | Codec |
 -------- ------- 
| 1      | H264  |
 -------- ------- 
| 2      | MPEG2 |
 -------- ------- 

Audio
 -------- ------- 
| FileID | Codec |
 -------- ------- 
| 1      | DTS   |
 -------- ------- 
| 1      | DTS   |
 -------- ------- 
| 1      | DTS   |
 -------- ------- 
| 2      | MP3   |
 -------- ------- 
| 2      | MP3   |
 -------- ------- 

Subtitle
 -------- ---------- 
| FileID | Language |
 -------- ---------- 
| 1      | en       |
 -------- ---------- 
| 1      | en       |
 -------- ----------

How to make my query to achieve what I need?

Thank you in advance for your help.

CodePudding user response：

I believe that the following is what you want:-

SELECT 
    m.ID AS num, 
    m.name AS movie, 
    (SELECT group_concat(codec,'-') FROM Video WHERE FileID = m.ID) AS v_codec, 
    (SELECT group_concat(codec,'-') FROM Audio WHERE FileID = m.ID) AS a_codec,
    (SELECT group_concat(Language,'-') FROM Subtitle WHERE FileID = m.ID) AS SUBs
FROM File AS m ORDER BY m.id;

Which produces :-

That is instead of basing the extract upon the cartesian product that would, without grouping, be some thing like :-

The answer does the group_concat only upon the relations