Problem with the joining of SQL tables via relationships

Question

I am currently having problems trying the run a query for some tables.

Below is what I am trying to do, I just can't seem to get it to work. I have also duplicated constituent and gifts to show you the relations between soft credit and gifts/constituents

Link to Table Relations Image

SELECT 
    Constituent.lookup_id,
    Constituent.name,
    SplitGifts.amount,
    SplitGifts.giftaidamount
FROM 
    dbo.Gifts Gifts
INNER JOIN dbo.Constituent Constituent
    ON Constituent.id = Gifts.constituent_id
INNER JOIN dbo.SplitGifts SplitGifts
    ON SplitGifts.giftid = Gifts.id
LEFT JOIN dbo.SoftCredit SoftCredit
    ON SoftCredit.giftid = Gifts.id
INNER JOIN dbo.Constituent Constituent_1
    ON Constituent_1.id = SoftCredit.constituentid
INNER JOIN dbo.Gifts Gifts_1
    ON Gifts_1.id = SoftCredit.giftid
INNER JOIN dbo.Package Package
    ON Package.id = SplitGifts.packageid
WHERE 
    Package.lookup_id = N'CORPCHAL'

Basically, I want the amount and gift_aid_amount from [SplitGifts] Constituent Name & lookup_id from [constituent] to show up for all Gifts however if a soft credit exists for that gift I need it to get the same fields via the [SoftCredit] table -> Gifts -> SplitGifts -> Fields

Answer 1

You could try if the query below works.

SELECT 
    Constituent.lookup_id,
    Constituent.name,
    SplitGifts.amount,
    SplitGifts.giftaidamount
FROM 
    dbo.Gifts Gifts
    LEFT JOIN dbo.SoftCredit SoftCredit ON SoftCredit.giftid = Gifts.id
    INNER JOIN dbo.Gifts Gifts_1 ON
        Gifts_1.id = SoftCredit.giftid OR
        (SoftCredit.giftid IS NULL AND Gifts_1.id = Gifts.id)
    INNER JOIN dbo.Constituent Constituent ON
        Constituent.id = SoftCredit.constituentid OR
        (SoftCredit.constituentid IS NULL AND Constituent.id = Gifts_1.constituent_id)
    INNER JOIN dbo.SplitGifts SplitGifts ON SplitGifts.giftid = Gifts_1.id
    INNER JOIN dbo.Package Package ON Package.id = SplitGifts.packageid
WHERE 
    Package.lookup_id = N'CORPCHAL'

It joins back to table Gifts (using alias Gifts_1) on the gift reference in SoftCredit or to itself if there is no SoftCredit.

Table Constituent is joined in a similar fashion: it joins on the value of SoftCredit.constituentid and when NULL, it falls back to Gifts_1.constituent_id.

All next joins regarding the gift should refer to Gifts_1 then.

I have not tested it though. But it might give you a hint in a possible solution direction.