CODEWITHSUNDEEP
sqlsql-servert-sql
user3120927
user3120927

Reputation: 17

Complex Join to the existing query

I want to add the below join to the existing query. I am not aware whether the logic is correct.

Any help would be appreciated.

  LEFT OUTER JOIN
  dbo.note not
  CASE
     WHEN not.main_ref_type='M'
     THEN pem.membership_id=not.main_ref_id
     WHEN not.main_ref_type=P'
     THEN per.person_id=not.main_ref_id
  END

Upvotes: 0

Views: 41

Answers (3)

Anon
Anon

Reputation: 10908

CROSS APPLY (
  VALUES
     ('M'     , pem.membership_id),
     ('P'     , per.person_id    )
) map(ref_type, ref_id           )
LEFT OUTER JOIN dbo.note not ON (
  not.main_ref_type = map.ref_type AND
  not.main_ref_id   = map.ref_id
)

Upvotes: 0

user2989408
user2989408

Reputation: 3137

Try this

LEFT OUTER JOIN dbo.note nt ON (nt.main_ref_type='M'
 AND pem.membership_id=nt.main_ref_id)
 OR 
 (nt.main_ref_type='P'
 AND per.person_id=nt.main_ref_id)

END

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You need an on clause. It should look more like this:

  LEFT OUTER JOIN
  dbo.note not
  on (not.main_ref_type='M' and not.main_ref_id = pem.membership_id) or
     (not.main_ref_type='P' and per.person_id=not.main_ref_id)

You should know that joins with or conditions often perform badly. In many cases, it is better to do two separate joins (to the note table) and then use logic in the select (typically coalesce()) to get the right values.

Upvotes: 2

Related Questions