"If value exists in table" - Is there such a thing?

database, mysql

Solution

You can get the list using `group_concat()`:

SELECT ID, group_concat(case when ITEM <> 'AA' then ITEM end)
FROM TABLE1 
group by id
having sum(item = 'AA') > 0;

The `case` statement ensures that the value is not included in the final list. `group_concat()` ignores `NULL` values.

The `having` clause makes sure that there is at least one `'AA'` value for the returned row.

EDIT:

Thanks to Praveen:

Fiddle here

Problem

I'm new to mysql and I need some help. I want to select all the rows from a table where a value exists, but not select that value: For example: ``` ID ITEM -------------------- 1 AA 1 22S 1 AB 2 F45 2 BB 3 1 3 1 3 AA 3 F45 3 F67 3 A ...... ``` something like: SELECT ID, ITEM FROM TABLE1 IF Item "AA" is present except "AA" and that would return: ``` 1 225,AB 3 1,1,F45,F67,A ``` What is the actual query for doing this? Thank you

Original source