How to return Y/N if product is either Red, Green, or Blue-CodePudding

I have a SQL table that looks like this (t1)

________________________________________________
|Id | ProductName | ColorID | ProductDescription|
|1  | x           | 7       | x                 |
-------------------------------------------------

And I have another table that contains the Colors (t2)

_________________
| Id | Color    | 
| 1  | Red      |
| 2  | Orange   |
| 3  | Yellow   |
| 4  | Green    |
| 5  | Blue     |
| .. | ...      |
-----------------

What I want to do is have a query that returns yes or no if the product is either Red, Green, or Blue depending on its colorID

____________________________
|Id | ProductName | Is_RGB |
| 1 | x           | N      |
----------------------------

This was what I initially did and it worked:

select t1.id as 'Id', 
t1.ProductName as 'ProductName',
case when t1.ColorID in ('1', '4', '5') then 'Y' else 'N' end as 'Is_RGB'
from t1

But is there a way that I can use the color names in the query instead of the colorId? Like a case when in ('red', 'green', 'blue') etc.... Please help. TIA!

CodePudding user response：

You can do it if willing to join. For example:

select t1.id as 'Id', 
t1.ProductName as 'ProductName',
case when t2.Color in ('Red', 'Green', 'Blue') then 'Y' else 'N' end as 'Is_RGB'
from t1
inner join t2
on ts.ColorID = t2.id

CodePudding user response：

    select 
    p.id,
    p.name,
    case 
        when c.name in('Red','Green','Blue') then 'Y' 
        else 'N' 
    end as color_flag
from product p
inner join color c on c.id=p.color_id;