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

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

Contents

Problem Description:

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!

Solution – 1

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
``````

Solution – 2

``````    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;
``````
Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.