Conditional Join

[smn198]

Hi All

I have 2 queries. I want to do a left out join from the mail query to the sub query only if the sub query meets the sub query criteria but if the sub query criteria are not met. I do not want to exclude it from the main query. Hope that makes sense!

Here are the 2 queries:
--- 1 ---
SELECT
"GiftMaster"."ID" AS MasterSysID,
"GiftMaster"."IMPORT_ID" AS MasterImportID,
"GiftMaster"."DTE" AS StartDate,
"GiftMaster"."Schedule_EndDate" AS EndDate,
MAX("GiftPayment"."DTE") AS LastPaymentDate,
"GiftMaster"."REF" AS Ref,
"GiftMaster"."REFERENCE_NUMBER" AS RefNo,
CASE "GiftMaster"."INSTALLMENT_FREQUENCY"
WHEN 1 THEN 12
WHEN 2 THEN 6
WHEN 3 THEN 3
WHEN 4 THEN 2
WHEN 5 THEN "GiftMaster"."Schedule_Spacing"
ELSE -1 END AS Frequency,
"Cons"."Constituent_ID" AS ConstituentID,
"Cons"."FIRST_NAME" AS FirstName,
"Cons"."LAST_NAME" AS Surname
FROM
(("re7_conversion"."dbo"."GIFT" "GiftMaster"
INNER JOIN "re7_conversion"."dbo"."Records" "Cons"
ON "Cons"."ID" = "GiftMaster"."Constit_ID")
INNER JOIN "re7_conversion"."dbo"."Constit_Address" "Address"
ON "Cons"."ID" = "Address"."Constit_ID")
LEFT OUTER JOIN
"re7_conversion"."dbo"."RecurringGiftActivity" "RecurringGiftActivity"
INNER JOIN "re7_conversion"."dbo"."GIFT" "GiftPayment"
ON "RecurringGiftActivity"."PaymentId"="GiftPayment"."ID"
ON "GiftMaster"."ID"="RecurringGiftActivity"."RecurringGiftId"
WHERE
"GiftMaster"."TYPE" = 30 AND
"GiftMaster"."PAYMENT_TYPE" = 5 AND
"GiftMaster"."GIFT_STATUS" = 1 AND
"GiftMaster"."REMIND_FLAG" = -1 AND
"Cons"."Deceased" = 0 AND
"Cons"."Inactive" = 0 AND
"Address"."Preferred" = -1 AND
"Address"."SendMail" = -1 AND
"Cons"."Constituent_ID" = '040000001'
GROUP BY
"GiftMaster"."ID",
"GiftMaster"."IMPORT_ID",
"GiftMaster"."DTE",
"GiftMaster"."Schedule_EndDate",
"GiftMaster"."REF",
"GiftMaster"."REFERENCE_NUMBER",
"GiftMaster"."INSTALLMENT_FREQUENCY",
"GiftMaster"."Schedule_Spacing",
"Cons"."Constituent_ID",
"Cons"."FIRST_NAME",
"Cons"."LAST_NAME"
ORDER BY
"Cons"."Constituent_ID"

--- 2 ---
SELECT
"GiftAttributes"."PARENTID" AS MasterSysID,
"AttributeTypes"."DESCRIPTION" AS AttrCat,
"TABLEENTRIES"."LONGDESCRIPTION" AS AttrDesc,
"GiftAttributes"."ATTRIBUTEDATE" AS AttrDate
FROM
("re7_conversion"."dbo"."GiftAttributes" "GiftAttributes"
INNER JOIN "re7_conversion"."dbo"."TABLEENTRIES" "TABLEENTRIES"
ON "GiftAttributes"."TABLEENTRIESID"="TABLEENTRIES"."TABLEENTRIESID")
INNER JOIN "re7_conversion"."dbo"."AttributeTypes" "AttributeTypes"
ON "GiftAttributes"."ATTRIBUTETYPESID"="AttributeTypes"."ATTRIBUTETYPESID"
WHERE
"AttributeTypes"."DESCRIPTION"='Reminders'

 

[smn198]

*Closed*