Trying to combine this queries into one, because I want to write queries without using temp table. Keep getting syntax error

Question

    SELECT
          CAST([patientDATA] AS XML).value('PatientcardCard[1]/Replacements[1]/patientId[1]', 'nvarchar(80)') [patientId]
        ,* into #tmp
        FROM hospital [c1] with(NOLOCK)
        where
        patientserial in (
        select ptserial From patients with(Nolock)
        where patientsid=6889
        and patientprogramid in (
        26917,
        21296,
        27025
        )
        )
    
    

    select patientId,patientbarcode,patientprogramID
    into #tmp1
    From #tmp
    join patients with(nolock) on patientserial=ptserial
    where patientid in
    ('0401478300007847',
    '0401478300008566',
    '0401478300008761',
    '0401478300008727',
    '0401478300007648',
    '0401478300008020'
)

So first query is using cast to get value from a tag inside a column, the tag is patientID, and it is storing data into a temp table.

The second query is pulling the data from the temp table. I want to write a nested query without using temp table. But I keep syntax error, this is my query so far:

select patientId,patientbarcode,patientprogramID
        
        From (
 SELECT
          CAST([patientDATA] AS XML).value('PatientcardCard[1]/Replacements[1]/patientId[1]', 'nvarchar(80)') [patientId]
        ,* into #tmp
        FROM hospital [c1] with(NOLOCK)
        where
        patientserial in (
        select ptserial From patients with(Nolock)
        where patientsid=6889
        and patientprogramid in (
        26917,
        21296,
        27025
        )
        )



)
        join patients with(nolock) on patientserial=ptserial
        where patientid in
        ('0401478300007847',
        '0401478300008566',
        '0401478300008761',
        '0401478300008727',
        '0401478300007648',
        '0401478300008020'
    )

My error in SQL:

invalid column in first line, around patientId,patientbarcode


and in join part, invalid column as ptserial

Is it because the value is casted?

Answer 1

There are some problems with your approach.

1. SELECT * is not a good idea. It also appears to be unnecessary for your final query.
2. You should reference which table each column is coming from.
3. You may be overthinking this.

SELECT CAST([patientDATA] AS XML).value('PatientcardCard[1]/Replacements[1]/patientId[1]', 'nvarchar(80)') [patientId]
, patientbarcode
, patiendprogramID

FROM hospital h
    INNER JOIN patients p ON p.ptserial = h.patientserial

WHERE patientsid = 6889
  AND patientprogramid IN (26917, 21296, 27025)
  AND patientid IN (
        '0401478300007847'
        ,'0401478300008566'
        ,'0401478300008761'
        ,'0401478300008727'
        ,'0401478300007648'
        ,'0401478300008020'
        )