CODEWITHSUNDEEP
mysql
bikedorkseattle
bikedorkseattle

Reputation: 981

MySQL GROUP BY on SELECT If Statement

I'm am trying to join a table that has a boolean column, and in my results I'm trying to get two columns from the join, one where the boolean is 0 and a separate on where it is 1.

Since there is a one to many relationship, I need to do a GROUP BY.

The following query returns the correct non GROUP BY results.

For each profile ID there is at least one row for both genre and influence.

SELECT p.profileID, IF( gpro.isInfluence =0, g.genreName, NULL ) AS genre, IF( gpro.isInfluence =1, g.genreName, NULL ) AS influence, g.genreName
FROM profiles p
LEFT JOIN genre_profiles gpro ON gpro.profileID = p.profileID
LEFT JOIN genres g ON g.genreID = gpro.genreID
WHERE g.genreName IS NOT NULL

However, when I do the corresponding GROUP BY/GROUP_CONCAT the output does not make sense.

SELECT p.profileID, IF( gpro.isInfluence =0, GROUP_CONCAT( g.genreName ) , NULL ) AS genre, IF( gpro.isInfluence =1, GROUP_CONCAT( g.genreName ) , NULL ) AS influence, g.genreName
FROM profiles p
LEFT OUTER JOIN genre_profiles gpro ON gpro.profileID = p.profileID
LEFT OUTER JOIN genres g ON g.genreID = gpro.genreID
WHERE g.genreName IS NOT NULL 
GROUP BY p.profileID

The above groups all the g.genreName rows in either of the columns, instead of them being split based on the gpro.isInfluence value. The column the result falls under I believe corresponds to the first result that is grouped.

1000001052  Latin American,Rock,Hip Hop NULL
1000001637  NULL    Electronic,Easy listening,Asian,popgun
1000001666  NULL    Electronic,Bacon,Emo,Modern folk,Hip Hop,R...

What I don't understand is why are the genre values are grouped together and not in their respective columns like the first query.

Upvotes: 2

Views: 4727

Answers (2)

Anish Agarwal
Anish Agarwal

Reputation: 1821

Here is a very basic statement to apply IF condition in Select statement with GROUP BY clause.

GROUP_CONCAT( IF (condition, 1, 0)) col

As GROUP_CONCAT return multiple , separated values. With the below line of statement we can avoid duplicate values

GROUP_CONCAT(DISTINCT IF (condition, 1, 0)) col

Upvotes: 0

bikedorkseattle
bikedorkseattle

Reputation: 981

SELECT p.profileID, GROUP_CONCAT( IF( gpro.isInfluence =0, g.genreName, NULL ) ) AS genre, GROUP_CONCAT( IF( gpro.isInfluence =1, g.genreName, NULL ) ) AS influence, g.genreName
FROM profiles p
LEFT OUTER JOIN genre_profiles gpro ON gpro.profileID = p.profileID
LEFT OUTER JOIN genres g ON g.genreID = gpro.genreID
WHERE g.genreName IS NOT NULL 
GROUP BY p.profileID

Putting the GROUP_CONCAT outside the IF statement gives the correct output, thanks to Ertunc for helping my brain make the pivot on that one.

Upvotes: 2

Related Questions