CODEWITHSUNDEEP
mysqlsqloracle-database
DanangFall
DanangFall

Reputation: 25

Getting data from another table, if exist will show as Yes else No

I am trying get a new column Claim_YN in a table. If there is data exists in the other table, then return 'yes' else return 'no'. Here's an example:

Product Table:

Product_ID
prod_1
prod_2
prod_3
prod_4
prod_5
prod_6

Claim Table:

CLAIM_PRODUCT_ID
Prod_1
Prod_2
Prod_6

I would to see these 2 columns combined and gives like in the output below:-

Product_ID Claim Y/N
prod_1 Yes
prod_2 Yes
prod_3 No
prod_4 No
prod_5 No
prod_6 Yes

So if a claim exists then the generated new column CLAIM_YN will return 'Yes', else it will return 'No'.

Can anyone help me on this? Thanks!

Upvotes: 0

Views: 1246

Answers (1)

The Impaler
The Impaler

Reputation: 48770

You can combine a LEFT JOIN with a CASE clause. For example:

select
  p.product_id,
  case when c.claim_product_id is not null then 'Yes'
    else 'No' end as claim_yn
from product p
left join claim c on c.claim_product_id = p.product_id

Upvotes: 1

Related Questions