sql - Correlated subqueries: How to get the ID of the outer query? -
i have multiple tables :
person --------------------- idperson, name stickertransaction --------------------- idtransac, idperson, idsomeoneelse, nbstickersreceived, nbstickersgiven purchase --------------------- idpurchase, idperson, nbstickersbought
i'm trying every person never made transaction or have 0 sticker. have number of stickers person has, here's formula :
nbstickersbought + nbstickersreceived - nbstickersgiven
here's query i've got far. problem i've got in subsubsubqueries... not have access person's id.
is there way have access person's id or there better way write query ?
select people.idperson, people.name ( -- selects people never made sticker transaction select p.idperson, p.name person p left join stickertransaction st on p.idperson = st.idperson st.idperson null union -- selects people have 0 sticker select p.idperson, p.name person p 0 = ( select sum(nbstickers) nbstickers ( -- not have access p.idperson here... select (st.nbstickersreceived - st.nbstickersgiven) nbstickers stickertransaction st st.idperson = p.idperson union -- not have access p.idperson here either... select pu.nbstickersbought nbstickers purchase pu pu.idperson = p.idperson ) ) ) people
afaik can't correlation derived table. can rewrite query non-correlated subquery:
select people.idperson, people.name ( -- selects people never made sticker transaction select p.idperson, p.name person p left join stickertransaction st on p.idperson = st.idperson st.idperson null union -- selects people have 0 sticker select p.idperson, p.name person p join ( select st.idperson, sum(nbstickers) nbstickers ( select (st.nbstickersreceived - st.nbstickersgiven) nbstickers stickertransaction st union select pu.nbstickersbought nbstickers purchase pu ) dt group st.idperson having sum(nbstickers) = 0 ) st on st.idperson = p.idperson ) people
Comments
Post a Comment