Home » SQL & PL/SQL » SQL & PL/SQL » show master details .. (9i , XP)
show master details .. [message #356890] Mon, 03 November 2008 00:50 Go to next message
adilsami
Messages: 46
Registered: October 2007
Member
I want show the master record , even if the detail record doesn't exists ..how i can do that .

When i pass value to (:assetno) it should show me the master record always ..

SELECT 
ASSETUSERCUST.PERSONID,
PERSON.DISPLAYNAME MYNAME,
AUTOATTRUPDATE.ASSET,
ASSET.DESCRIPTION ASSET_DESC,
ASSET.LOCATION,
AUTOATTRUPDATE.WONUM,
WORKORDER.DESCRIPTION WO_DESC,
ASSETATTRIBUTE.DESCRIPTION ATTR_DESC,
AUTOATTRUPDATE.OLDALNVALUE,
AUTOATTRUPDATE.NEWALNVALUE,
AUTOATTRUPDATE.COMPLETEDATETIME


FROM
AUTOATTRUPDATE,
ASSETUSERCUST,
PERSON,
ASSET,
WORKORDER,
ASSETATTRIBUTE

WHERE ASSETUSERCUST.ASSETNUM =AUTOATTRUPDATE.ASSET 
and PERSON.PERSONID = ASSETUSERCUST.PERSONID 
AND ASSET.ASSETNUM = AUTOATTRUPDATE.ASSET 
AND WORKORDER.WONUM = AUTOATTRUPDATE.WONUM
AND AUTOATTRUPDATE.ATTRIBUTE = ASSETATTRIBUTE.ASSETATTRID
and PERSON.PERSONID = ASSETUSERCUST.PERSONID
and 1=1
and asset.assetnum=:assetno
order by ATTR_DESC,AUTOATTRUPDATE.COMPLETEDATETIME



(Master) fields design :

Asset No
Description
Location
Custodian


(Detail) fields design :


WO No.
WO Desc
Attribute Desc
Old Value
New Value
Modify Date

Re: show master details .. [message #356896 is a reply to message #356890] Mon, 03 November 2008 00:54 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You want to "select from master where not exists an associated row in detail"

Regards
Michel
Previous Topic: Dead Lock In Oracle
Next Topic: help in regard to oracle web connectivity
Goto Forum:
  


Current Time: Sun Dec 04 14:55:56 CST 2016

Total time taken to generate the page: 0.18321 seconds