Correlated subqueries: How to get the ID of the outer query?

Question

I have multiple tables :

Person
---------------------
IDPerson, Name

StickerTransaction
---------------------
IDTransac, IDPerson, IDSomeoneElse, NbStickersReceived, NbStickersGiven

Purchase
---------------------
IDPurchase, IDPerson, NbStickersBought

I'm trying to get every person who never made a transaction or currently have 0 sticker. To have the number of stickers a person has, here's the formula :

NbStickersBought + NbStickersReceived - NbStickersGiven

Here's the query I've got so far. The problem I've got is that in the subsubsubqueries... I do not have access to the person's ID.

Is there a way to have access to the person's ID or is there a better way to do write this query ?

SELECT People.IDPerson, People.Name
FROM
(
    -- Selects people that never made a sticker transaction

    SELECT p.IDPerson, p.Name
    FROM Person p
    LEFT JOIN StickerTransaction sT
    ON p.IDPerson = sT.IDPerson
    WHERE sT.IDPerson IS NULL

    UNION

    -- Selects people that have currently 0 sticker

    SELECT p.IDPerson, p.Name
    FROM Person p
    WHERE 0 = (
                SELECT SUM(NbStickers) AS NbStickers
                FROM (

                       -- I do not have access to p.IDPerson here...

                       SELECT (sT.NbStickersReceived - sT.NbStickersGiven) AS NbStickers
                       FROM StickerTransaction sT
                       WHERE sT.IDPerson = p.IDPerson

                     UNION ALL

                       -- I do not have access to p.IDPerson here either...

                       SELECT pu.NbStickersBought AS NbStickers
                       FROM Purchase pu
                       WHERE pu.IDPerson = p.IDPerson
                     )
              )
) People

Answer 1

AFAIK you can't do a correlation into a Derived Table. But you can rewrite your query to a non-correlated subquery:

SELECT People.IDPerson, People.NAME
FROM
(
    -- Selects people that never made a sticker transaction

    SELECT p.IDPerson, p.NAME
    FROM Person p
    LEFT JOIN StickerTransaction sT
    ON p.IDPerson = sT.IDPerson
    WHERE sT.IDPerson IS NULL

    UNION

    -- Selects people that have currently 0 sticker

    SELECT p.IDPerson, p.NAME
    FROM Person p
    JOIN     (
                SELECT sT.IDPerson, SUM(NbStickers) AS NbStickers
                FROM (

                       SELECT (sT.NbStickersReceived - sT.NbStickersGiven) AS NbStickers
                       FROM StickerTransaction sT

                     UNION ALL

                       SELECT pu.NbStickersBought AS NbStickers
                       FROM Purchase pu
                     ) dt
                GROUP BY sT.IDPerson
                HAVING SUM(NbStickers) = 0
             ) sT
    ON sT.IDPerson = p.IDPerson

) People