How to select all values, not just those in where clause

Question

I have a query that looks like this:

SELECT id, GROUP_CONCAT(type SEPARATOR ',') AS type FROM rock_types 
        WHERE type IN("DWS","Top rope") GROUP BY id

this returns all the ids that have a type of DWS or top rope or both with the types concatenated. However, there are more types than just those 2. Is it possible to still select only ids that have DWS or top rope or both, but also select all the other types associated with those ids? Thanks!

Answer 1

Using a subquery (would work in oracle, and instr works in mysql too... not sure about subquery syntax for mysql though)

Select id, type from 
(SELECT id, GROUP_CONCAT(type SEPARATOR ',') AS type FROM rock_types 
        GROUP BY id) a
WHERE INSTR(type,"DWS")>0 OR INSTR(type,"Top rope")>0