CODEWITHSUNDEEP
phpmysqlsqlgroup-concat
Marco
Marco

Reputation: 2737

Can i use 2 combinations of group concat? or should i go with subqueries

I have a personal project were I'm building my home discography. I'm trying to figure out how to do a query. I have 5 tables:

album

albumid   name 
------------------
1         name1
2         name2    
3         name3    
4         name4    
5         name5    
6         name6     
7         name7

discformat

formatid    format
-----------------------
1           CD
2           DVD
3           CASSETTE

label

labelid    label
------------------------
1           SONY
2           EMI
3           COLUMBIA
4           PHANTOM
5           UNIVERSAL

album_disc

(an album contains multiple disc and a disc belong to only one album. A disc has only one format)

discid  albumid    formatid
---------------------------
1        1         1
2        1         2
3        7         3
4        5         1

album_label

(an album has multiple recordlabels and a record label could be in many albums)

albumid   labelid
-----------------
1         1
1         4
5         5
4         1

let's say a pick album 1, i would like to see the results has:

albumid  name     format     label
-------------------------------------------
1        name1    CD, DVD    SONY, PHANTOM

I've tried using group concat, but it only works for the 'format'. Once i add another group concat for the label, it doesn't work. I've read that maybe the solution is using subqueries , but I've tried every combination and, because i'm a newbie, I'm not having a lot of success. Can i use 2 combinations of group_concat? If not, how do i go about?

Thanks,
Marco

Upvotes: 1

Views: 75

Answers (1)

John Woo
John Woo

Reputation: 263843

You need to join all the tables. Use GROUP_CONCAT (with DISTINCT to select only unique values), and grouped them by albumID

SELECT  a.albumID, a.Name,
        GROUP_CONCAT(DISTINCT c.format) formats,
        GROUP_CONCAT(DISTINCT e.`label`) labels
FROM    album a
        INNER JOIN album_disc b
            ON a.albumID =  b.albumid
        INNER JOIN discformat c
            ON b.formatid = c.formatID
        INNER JOIN album_label d
            ON a.albumid = d.albumID
        INNER JOIN label e
            ON e.labelid = d.labelid
WHERE a.albumID = 1         // remove this line if you want to select all records
GROUP BY a.albumID, a.Name

SQLFiddle Demo

Upvotes: 3

Related Questions