Skip to Content (Press Enter)
2021-11-22 00-14-28
Leigh Rogers
45Views
3Comments
Advanced SQL - Multiple joins to the same table
Question
SQL
Reactive
Application Type
Reactive

Hi

I have the below query, wish I want to expand. Currently it returns

LEN({Patient_Letters}.[Binary_File]), {Patient_Letters}.[Id]

Previously there's only been one entry in the Patient_Letters table, per referral. There's an entity called LetterType, all of which have the value 1, though, that's not referenced here because it's not previously been relevant.

There's now two letters in this table, per referral. One with LetterType = 1, and the other with LetterType =2.

I want to expand this query, so it returns

LEN({Patient_Letters}.[Binary_File]), {Patient_Letters}.[Id]

for both letters, but marks one as "Incoming" and one as "Outcome".I would be very grateful if you could help me with this.

Thank you!

SELECT
{Patient_Referrals}.Id,
{Patient_Referrals}.[Referral_DateTime],
{Pathways}.[Name],
LEN({Patient_Referral_Letters}.[Binary_File]),
{Patient_Referral_Letters}.[Id],
{Patient_Referrals}.[Awaiting_Sec_Intervention],
{Patient_Referrals}.[Receiver_Read_DateTime]
FROM
{Patient_Referrals}
JOIN
{Patient_Attendances} on {Patient_Referrals}.[Attendance_ID] = {Patient_Attendances}.Id
JOIN {Pathways} on {Pathways}.[Id] = {Patient_Referrals}.[Service_ID]
LEFT JOIN {Patient_Referral_Letters} on {Patient_Referral_Letters}.[Referral_ID] = {Patient_Referrals}.Id
JOIN {Patients} ON {Patients}.[id] = {Patient_Attendances}.[Patient_ID]WHERE {Patients}.[id] = @PatientId 

0
0
08 Aug 2024
Copy Link
2021-09-06 15-09-53
Dorine Boudry
 
MVP

Hi @Leigh Rogers ,

It is perfectly possible to join twice

so I'm not entirely sure of your datamodel, but I'm assuming the Patient_Referral_Letters has a reference attribute for LetterType static entity?

the key is 

  • give each instance of the joined table a different name with 'as' 
  • and then refer to it with that name everywhere.  
  • add the selection on the correct type as part of the join condition

It will be something like

SELECT {Patient_Referrals}.Id,
       {Patient_Referrals}.[Referral_DateTime],
       {Pathways}.[Name],
       LEN(incoming.[Binary_File]),
       incoming.[Id],
       LEN(outgoing.[Binary_File]),
       outgoing.[Id],
       {Patient_Referrals}.[Awaiting_Sec_Intervention],
       {Patient_Referrals}.[Receiver_Read_DateTime]
FROM   {Patient_Referrals}
JOIN   {Patient_Attendances} on {Patient_Referrals}.[Attendance_ID] = {Patient_Attendances}.Id
JOIN   {Pathways} on {Pathways}.[Id] = {Patient_Referrals}.[Service_ID]
LEFT JOIN {Patient_Referral_Letters} as incoming 
              on incoming.[Referral_ID] = {Patient_Referrals}.Id 
              and incoming.LetterTypeId = 1
LEFT JOIN {Patient_Referral_Letters} as outgoing 
              on outgoing.[Referral_ID] = {Patient_Referrals}.Id 
              and outgoing.LetterTypeId = 2
JOIN   {Patients} ON {Patients}.[id] = {Patient_Attendances}.[Patient_ID]
WHERE {Patients}.[id] = @PatientId 


Dorine

1
0
08 Aug 2024
Copy Link
2020-09-15 13-07-23
Kilian Hekhuis
 
MVP

Note that "as" is optional. Also note that Service Studio is very bad wat aliasses (as the "different names" are called), so you'll have to type the attribute names yourself (no autocomplete).

1
0
09 Aug 2024
Copy Link
2020-09-15 13-07-23
Kilian Hekhuis
 
MVP

Hi Leigh,

Whenever you can, use Aggregates instead of SQL. With Aggregates, you can more easily use aliasses. Given the simplicity of your query, an Aggregate should definitely be the way to go!

1
0
09 Aug 2024
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.