Group concat - In MySQL how can I concatenate multiple rows and then search on that data

Questions

Struggling a bit with MySQL. I ve found the GROUP_CONCAT function which looks right for my needs, but I can t work out how to use it. What I d like to do is search in several columns as well as the concat column; so if any of the columns equal my search term (%a% in this example) it should be returned. I ve worked out how to return the concatenated genre string but I can t work out how to search on it.

SELECT albumArtworkURL, albumName, albumID, 
  b.artistID AS ArtistID, b.artistName AS ArtistName,
  GROUP_CONCAT(DISTINCT c.songGenre separator  , ) AS genres
FROM album
LEFT JOIN artist b ON album.albumArtist = b.artistID
LEFT JOIN song c ON albumID = c.songOnAlbum
WHERE albumName LIKE  %a%  OR albumYear LIKE  %a% 
GROUP BY albumArtworkURL, albumName, albumID, ArtistID, ArtistName
ORDER BY albumYear ASC, albumName ASC

Would really appreciate some help. Thanks :-)

Answers

If you want to search on the result of the GROUP_CONCAT(), then you could wrap your query in another select:

SELECT *
FROM
(
  SELECT albumArtworkURL, 
    albumName, 
    albumID, 
    b.artistID AS ArtistID, 
    b.artistName AS ArtistName,
    GROUP_CONCAT(DISTINCT c.songGenre separator  , ) AS genres
  FROM album
  LEFT JOIN artist b 
    ON album.albumArtist = b.artistID
  LEFT JOIN song c 
    ON albumID = c.songOnAlbum
  GROUP BY albumArtworkURL, albumName, albumID, ArtistID, ArtistName
) src
WHERE albumName LIKE  %a%  
  OR albumYear LIKE  %a% 
  OR genres LIKE   %a% 
ORDER BY albumYear ASC, albumName ASC

But unless I am missing something in your explanation, I don t know why you can t just search on the songGenre instead of the concat version of it:

SELECT albumArtworkURL, 
  albumName, 
  albumID, 
  b.artistID AS ArtistID, 
  b.artistName AS ArtistName,
  GROUP_CONCAT(DISTINCT c.songGenre separator  , ) AS genres
FROM album
LEFT JOIN artist b 
  ON album.albumArtist = b.artistID
LEFT JOIN song c 
  ON albumID = c.songOnAlbum
WHERE albumName LIKE  %a%  
  OR albumYear LIKE  %a% 
  OR songGenre LIKE  %a% 
GROUP BY albumArtworkURL, albumName, albumID, ArtistID, ArtistName
ORDER BY albumYear ASC, albumName ASC

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/14491198/in-mysql-how-can-i-concatenate-multiple-rows-and-then-search-on-that-data

Related