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

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?

CodePudding user response：

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

CodePudding user response：

You should have a foreign key on citizen table like "AdminId" , then your select query changes to this :

SELECT ctz.Name, ctz.AdminId, ad.Name
FROM Citizen ctz
INNER JOIN Admin ad
ON ctz.AdminId = ad.Id
WHERE ctz.Id = 2