return a table with a column with yes or no

I have the following two tables:

Singers

identification    name
-------------------------
      20          Bolton
      21          Madonna
      22          Nirvana
      23          Hendrix

Colaborate

first_singer_id     second_singer_id       genre
---------------------------------------------------
      20                  21               pop
      21                  23               pop
      22                  21               rock
      23                  20               rock
      23                  21               metal
      23                  21               hiphop
      23                  22               pop

I want to get a result where every name of the singers table has stated in a second column whether or not they sing metal. So for example Hendrix and Madonna sing metal so the table should look like this:

name     metal
--------------
Bolton   N
Madonna  Y
Nirvana  N
Hendrix  Y

I have tried the following but I don’t get distinct results nor does it take into account the symmetric relation of Colaborate.

select case when C.genre= 'metal' then "Y" else "N" end as genre, S1.name 
from   Colaborate C
       JOIN Singers S1
       ON S1.identification = C.first_singer_id
       JOIN Singers S2
       ON S2.identification = C.second_singer_id

How can I solve this with a CASE expression?

Answer

You can ascertain this a number of ways, one way would be using exists

Select name,
  case when exists (select * from colaborate c where c.first_singer_id=s.identification and genre='metal')
         or exists (select * from colaborate c where c.second_singer_id=s.identification and genre='metal')
  then 'Y' else 'N' end as Metal
from Singers s

Advertisement

Answer