[Last Call] Learn how to a build a cloud-first strategyRegister Now


specify yorn if field is populated in other relationsal table

Posted on 2007-10-09
Medium Priority
Last Modified: 2012-06-27

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

Question by:rutgermons
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20043105
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
LVL 14

Accepted Solution

GGuzdziol earned 2000 total points
ID: 20043262
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question