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 :-)
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
http://stackoverflow.com/questions/14491198/in-mysql-how-can-i-concatenate-multiple-rows-and-then-search-on-that-data