I have a complicated query that is based on a series of JOINS between various parent and child tables. Basically, the part of the query that is giving me the biggest headaches is with the OUTER JOIN between two sets of tables.
Here is the query:
SELECT DISTINCT(PO.PartnerOfferID),
DS.DemographicID,
O.*,
OD.*,
OS.*,
P.PersonID,
P.BirthDate,
PD.PersonID
FROM tbl_Demo_Spec DS,
tbl_Offer O,
tbl_Offer_Demo OD,
tbl_Offer_Spec OS,
tbl_Offer_Site OST,
tbl_Person P,
tbl_Person_Demo PD,
tbl_PartnerOffers PO
WHERE (O.Offer_Page = 1)
AND (O.Offer_Hidden = 0)
AND (O.Offer_ExpireDate <= (sysdate))
AND (O.Offer_Active = 1)
AND (OS.OfferTypeID = 1)
AND (OST.SiteID = 2)
AND (P.PersonID = 245)
AND (O.OfferID = OS.OfferID)
AND (O.OfferID = OST.OfferID)
AND (O.OfferID = OD.OfferID)
AND (O.OfferID = PO.OfferID)
AND (OD.DemoID = PD.DemoID(+))
AND (OD.DemoID = DS.DemoID(+))
ORDER BY O.Offer_Page
For those not familiar with Oracle syntax, the last two conditions in the WHERE clause are OUTER JOINS denoted by (+). Also, in Oracle, JOINS use the (Tbl1.PK -> Tbl2.FK) construct.
I have tried UNION and UNION ALL functions, which didn't make any difference in the rowcount returned, other than taking much longer to process.
The basic function of this query are:
1) Pull offers based on Page #, Date, Active, Type and their associated PartnerOfferID (which is a 1->N between Offers and PartnerOffers with the FK of OfferID).
2) Check the offer demographics against person demographics, which are based on an "ID" - DemoID (superkey value of DemographicID and DemoAnswerID).
If anyone can spot any gross errors with the above query, I'd greatly appreciate...I'll keep plugging away at my PL/SQL book in the meantime.
========================================
Buh Bye!
Cheers,
Me
Here is the query:
Code:
SELECT DISTINCT(PO.PartnerOfferID),
DS.DemographicID,
O.*,
OD.*,
OS.*,
P.PersonID,
P.BirthDate,
PD.PersonID
FROM tbl_Demo_Spec DS,
tbl_Offer O,
tbl_Offer_Demo OD,
tbl_Offer_Spec OS,
tbl_Offer_Site OST,
tbl_Person P,
tbl_Person_Demo PD,
tbl_PartnerOffers PO
WHERE (O.Offer_Page = 1)
AND (O.Offer_Hidden = 0)
AND (O.Offer_ExpireDate <= (sysdate))
AND (O.Offer_Active = 1)
AND (OS.OfferTypeID = 1)
AND (OST.SiteID = 2)
AND (P.PersonID = 245)
AND (O.OfferID = OS.OfferID)
AND (O.OfferID = OST.OfferID)
AND (O.OfferID = OD.OfferID)
AND (O.OfferID = PO.OfferID)
AND (OD.DemoID = PD.DemoID(+))
AND (OD.DemoID = DS.DemoID(+))
ORDER BY O.Offer_Page
For those not familiar with Oracle syntax, the last two conditions in the WHERE clause are OUTER JOINS denoted by (+). Also, in Oracle, JOINS use the (Tbl1.PK -> Tbl2.FK) construct.
I have tried UNION and UNION ALL functions, which didn't make any difference in the rowcount returned, other than taking much longer to process.
The basic function of this query are:
1) Pull offers based on Page #, Date, Active, Type and their associated PartnerOfferID (which is a 1->N between Offers and PartnerOffers with the FK of OfferID).
2) Check the offer demographics against person demographics, which are based on an "ID" - DemoID (superkey value of DemographicID and DemoAnswerID).
If anyone can spot any gross errors with the above query, I'd greatly appreciate...I'll keep plugging away at my PL/SQL book in the meantime.
========================================
Buh Bye!
Cheers,
Me