SQL – How to substitute a result which was retrieved by a SELECT clause

October 11, 2022

Suppose you have 2 tables,

Citizen
Admin

An Admin is inherently a Citizen, so Admin has a FK into Citizen’s PK.
Next when a Citizen entry is created, the entry records which Admin entered it.

Citizen includes details like the citizen’s name, whereas admin includes other details not related to a citizen’s details.

So then I have a query,

SELECT ctzName, ctzEnteredByID
FROM Citizen
INNER JOIN Admin
ON ctzPK = admPK
WHERE ctzPK = 2

This query returns the name of the Citizen and the ID of the Admin who created the Citizen entry. In this case that ID would be admPK/ctzPK of the Admin. As an example, let’s say the Citizen table has 2 entries,

ctzPK = 1, ctzName = Chris, ctzEnteredByID = 0
ctzPK = 2, ctzName = John, ctzEnteredByID = 1

then this query returns,

John, 1

However, I want to print the name of the Admin who entered it as well. So I want my result to be

John, 1, Chris

I’ve tried SELECTing this value as a condition in WHERE, but to no avail. How do you do this type of substitution?

>Solution :

Just add another two INNER JOIN (and alias the extant table references) to get the data for the subjectCitizen.ctzEnteredByID.

Like so:

SELECT

    subjectCitizen.ctzName        AS "Subject Citizen Name",
    subjectCitizen.ctzEnteredByID AS "Subject Citizen's data entered by (ID)",
    makerCitizen.ctzName          AS "Subject Citizen's data entered by (Name)"

FROM
    -- Subject data:
    dbo.Citizen AS subjectCitizen
    INNER JOIN dbo.Admin AS subjectAdmin ON
        subjectCitizen.ctzPK = subjectAdmin.admPK

    -- Subject's creator:
    INNER JOIN dbo.Admin AS maker ON
        maker.admPK = subjectCitizen.ctzEnteredByID

    INNER JOIN dbo.Citizen AS makerCitizen ON
        maker.admPK = makerCitizen.ctzPK

WHERE
    subjectCitizen.ctzPK = 2