Easy JOIN question for the pros

From: James <geniosity_at_gmail.com>
Date: 6 Jun 2005 08:57:21 -0700
Message-ID: <1118073441.670798.113980@f14g2000cwb.googlegroups.com>

Hi,

I'd like to return all rows in my table, but the 3rd column must only contain the "VALUE" (OIDP_VALUE) when "(LOWER(TBL5.OIDP_DOMAIN) = LOWER('JACK'))". If I run the query below as is, I ONLY get data where the above clause is true. There are 3 possible VALUES, so I don't want to return all of them:

---

SELECT DISTINCT TBL1.mainID,

	 TBL2.ap_UniqueName,
	 TBL5.OIDP_VALUE
FROM PurchaseOrderTab TBL1,
	 ApprovableTab TBL2,
	 POLineItemTab TBL3,
	 SupplierTab TBL4,
	 us_OrganizationIDPartTab TBL5

WHERE TBL2.mainID = TBL1.mainID

AND TBL1.po_LineItems = TBL3.lvId
AND TBL1.mainID = TBL3.mainID
AND TBL3.poli_Supplier = TBL4.mainID
AND TBL4.oid_Ids = TBL5.lvId
AND TBL4.mainID = TBL5.mainID

AND (LOWER(TBL5.OIDP_DOMAIN) = LOWER('JACK')) AND (TBL2.ap_Active = 1)

---

Any help will be appreciated.

Thanks,
James Received on Mon Jun 06 2005 - 10:57:21 CDT