CODEWITHSUNDEEP
sql-serverjointemp-tables
regents
regents

Reputation: 626

Accessing a new table created after a join

I have joined select columns from 3 tables into a new table using the following:

    SELECT A.ExternalID, A.UserDefinedXml.value('(Skin_Sheet/@Label)[1]', 'varchar(3)') AS SS, A.ServiceSiteUid, A.LastModifiedDate, A.PersonUid,
       B.FirstName, B.LastName, B.PersonUid,
       C.Name

  FROM Patient A
  INNER JOIN Person B ON B.PersonUid = A.PersonUid
  INNER JOIN ListServiceSite C ON C.ServiceSiteUid = A.ServiceSiteUid
  WHERE SS IS NOT NULL
  ORDER By LastModifiedDate;

This all works but I'm not sure how to reference the column SS created from data extracted from the XML so I can only select the observations in which the value is "Yes" or "No". In R I would have created a new object but I'm not sure how SQL stores this new table if I don't specify what the table name is.

Side note, I did try to insert this into a new table but SQL wasn't letting me because, for some reason, the join resulted in PersonUid being duplicated. enter image description here

Thank you in advance, I'm very, very new to SQL and trying to learn on the fly.

Upvotes: 0

Views: 858

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88996

Conceptually WHERE comes before SELECT, so you need to push the query into a derived table subquery or Common Table Expression (CTE) to reference SS in a WHERE clause. EG

with q as
(
    SELECT A.ExternalID, A.UserDefinedXml.value('(Skin_Sheet/@Label)[1]', 'varchar(3)') AS SS, A.ServiceSiteUid, A.LastModifiedDate, A.PersonUid,
       B.FirstName, B.LastName, B.PersonUid,
       C.Name

  FROM Patient A
  INNER JOIN Person B ON B.PersonUid = A.PersonUid
  INNER JOIN ListServiceSite C ON C.ServiceSiteUid = A.ServiceSiteUid
)
SELECT *
FROM q
WHERE SS IS NOT NULL
ORDER By LastModifiedDate;

Upvotes: 3

digital.aaron
digital.aaron

Reputation: 5707

This will put your results into a temp table, and avoids the problem of having two columns with the same name:

SELECT
    A.ExternalID
    ,SS = A.UserDefinedXml.value('(Skin_Sheet/@Label)[1]', 'varchar(3)')
    ,A.ServiceSiteUid
    ,A.LastModifiedDate
    ,PersonUid_A = A.PersonUid
    ,B.FirstName
    ,B.LastName
    ,PersonUid_B = B.PersonUid
    ,C.Name
INTO #TempResults
FROM Patient A
INNER JOIN Person B ON B.PersonUid = A.PersonUid
INNER JOIN ListServiceSite C ON C.ServiceSiteUid = A.ServiceSiteUid
WHERE SS IS NOT NULL
ORDER BY LastModifiedDate;

Upvotes: 0

Related Questions