-1

I'm working with Microsoft SQL Server. I've tried to find something similar but I don't understand how to adapt it to my code so I would be grateful for your help with some code samples.

Table 1 - 'EMER'

Patient             AdDate             AdNum              MedRec   
-----------------------------------------------------------------
A                 2022-06-17            4356             5456744
B                 2022-06-17            2345             5423908
C                 2022-06-17            3324             5421763

Table 2 - 'DESCRIP'

Patient            MedRec         NurseAssesment
------------------------------------------------------
A                 5456744         text text text
C                 5421763         text text text

what I want a joined table including rows that don't match

how can I do it if 'B' doesn't exist at all at table 2?

patient B has not seen a nurse so he doesn't exist on table 2, not even NULL value. I want the option to show that he hasn't seen the nurse hence to give him a NULL value.

Table 3 -

Patient             AdDate             AdNum              MedRec        NurseAssesment     
---------------------------------------------------------------------------------------
A                 2022-06-17            4356             5456744        text text text
B                 2022-06-17            2345             5423908        NULL
C                 2022-06-17            3324             5421763        text text text

Second question: after I created the joined table above (Table 3), how can I add a column of binary option if the patient has NULL value in 'NurseAssesment' so give him 0, else 1:

Table 4 -

Patient             AdDate             AdNum              MedRec        NurseAssesment         HasSeenNurse     
-----------------------------------------------------------------------------------------------------------
A                 2022-06-17            4356             5456744        text text text              1
B                 2022-06-17            2345             5423908        NULL                        0
C                 2022-06-17            3324             5421763        text text text              1
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
KapSht
  • 157
  • 8
  • Does this answer your question? [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) – Stu Jun 19 '22 at 08:22

1 Answers1

0

It seems like you want to use LEFT JOIN between EMER table and DESCRIP table, while keeping the left side in case you do not have any records in the right, by matching the MedRec records.

SELECT e.*,
d.NurseAssesment,
CASE WHEN NurseAssesment IS NOT NULL THEN 1 ELSE 0 END AS HasSeenNurse
FROM EMER e
LEFT JOIN DESCRIP d
ON e.MedRec = d.MedRec
Bogner Roy
  • 199
  • 7