specify yorn if field is populated in other relationsal table


i have two tables

workorder and asset

in my asset table I have data like so

asset    contract
car1      waranty1
car1      waranty2
car2      null
car3      repair

in my workorder table i have three fields

workid     asset  description
1             car1    broken
2             car2    inop

how can i add a field into my query to state in YORN form if the contract field is present
to that specific asset in my asset table like so

workid     asset  description    contracts exist?
1               car1    broken              Y
2               car2    inop                  N

All help will do

Who is Participating?
GGuzdziolConnect With a Mentor Commented:
select wo.workid, wo.asset, wo.description,
    decode(max(a.contract), null, 'N', 'Y')
  from workorder wo
    inner join asset a on wo.asset = a.asset
  group by wo.workid, wo.asset, wo.description
Guy Hengel [angelIII / a3]Billing EngineerCommented:
select a.*, case when w.workid is null then 'N' else 'Y' end contract_exists
from asset a
left join workorder w
  on w.asset = a.asset
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.