Find What Column i Joined on when using OR

Question

In My database I have a row with multiple Codes, EG:

ID,Code1,Code2,Code3,Code4

These codes reference a Procedure Name in another table EG:

Code1 = 'Procedure one'
Code2 = 'Procedure two'
ect.

I needed to convert this single row to show one line for every code, and corresponding procedure name EG:

ID,ProcedureName
1,'Procedure One'
1,'Procedure two'
2,'Procedure one'

To get this to work I'm using an outer join with an OR statement, not the most performance effective, but since the ProcedureName Table isn't that large, i'm not too fussed about overhead at the moment, more about getting it to work.

FROM Events EV
LEFT JOIN ProcedureName PN
        ON (PN.CODE = Ev.Code1)
           OR (PN.CODE = Ev.Code2)
           OR (PN.CODE = Ev.Code3)
           OR (PN.CODE = Ev.Code4)

This works, however Now I have the problem of being able to tell What procedure is the Primary, and Secondary. Usually the Primary/secondary is denoted purely by whatever one is in the first Code. IE the primary would be whatever is in Code1, secondary in code2, ect.

However since I have now Joined using an OR, i now have no idea what Code that the procedure has joined to.

I've thought of just doing a case statement

CASE
           WHEN PN.CODE = Ev.Code1 THEN
               '(Primary) ' + ISNULL(PN.NAME, '')
           WHEN PN.CODE = Ev.Code2 THEN
               '(Secondary) ' + ISNULL(PN.NAME, '')
           WHEN PN.CODE = Ev.Code3 THEN
               '(Tertiary) ' + ISNULL(PN.NAME, '')
           WHEN PN.CODE = Ev.Code4 THEN
               '(Quaternary) ' + ISNULL(PN.NAME, '')
       END AS ProcedureName,

However this has the major issue of, on the off chance, that both code1 and code2 are the same code. Which means they will both show up as primary.

Can anyone give me any hints as to how to find out what the OR join actually Joined on? did it join on code1, code2? is there perhaps a better way to write the join that will allow me to have multiple lines per ID (depending on amount of codes) whilst still allowing me to find out where they are Code1 or code2?

If you simply want to transpose a fixed number of columns in to rows you can use UNPIVOT: https://stackoverflow.com/questions/18026236/sql-server-columns-to-rows — Alex K., Jun 26 '18 at 11:46
I'd vote against UNPIVOT messy syntax and would recommend cross apply as per one of the answers — George Menoutis, Jun 26 '18 at 11:57

MatBailie · Answer 1 · 2018-06-26T12:31:50.897

I would reword the question slightly. In reality the it doesn't "join on a column", it joins on the result of a boolean expression.

So, what you want is to find out which parts of the boolean expression are true or not...

SELECT
  *,
  CASE WHEN PN.CODE = Ev.Code1 THEN 1 ELSE 0 END   AS MatchingCode1,
  CASE WHEN PN.CODE = Ev.Code2 THEN 1 ELSE 0 END   AS MatchingCode2,
  CASE WHEN PN.CODE = Ev.Code3 THEN 1 ELSE 0 END   AS MatchingCode3,
  CASE WHEN PN.CODE = Ev.Code4 THEN 1 ELSE 0 END   AS MatchingCode4
FROM
  Events EV
LEFT JOIN
  ProcedureName PN
    ON  PN.CODE IN (Ev.Code1, Ev.Code2, Ev.Code3, Ev.Code4)

If you want that as a single column, you could use binary arithmetic.

SELECT
  *,
  CASE WHEN PN.CODE = Ev.Code1 THEN 1 ELSE 0 END +
  CASE WHEN PN.CODE = Ev.Code2 THEN 2 ELSE 0 END +
  CASE WHEN PN.CODE = Ev.Code3 THEN 4 ELSE 0 END +
  CASE WHEN PN.CODE = Ev.Code4 THEN 8 ELSE 0 END    AS MatchingCodes
FROM
  Events EV
LEFT JOIN
  ProcedureName PN
    ON  PN.CODE IN (Ev.Code1, Ev.Code2, Ev.Code3, Ev.Code4)

Here a value of 1 in MatchingCodes means that Code1 is a match. Similarly a value of 3 means Code1 and Code2 both match, or a value of 15 means that all the codes match.

EDIT: (After making it clear that you want multiple rows)

This is similar to Gordon's answer, but has slightly different behaviour; you get 1 row per match instead of 4 rows all the time, or one row with NULLs if there is no match.

SELECT
  *
FROM
  Events   EV
OUTER APPLY
(
  SELECT 1 AS MatchedCode, * FROM ProcedureName WHERE CODE = EV.Code1
  UNION ALL
  SELECT 2 AS MatchedCode, * FROM ProcedureName WHERE CODE = EV.Code2
  UNION ALL
  SELECT 3 AS MatchedCode, * FROM ProcedureName WHERE CODE = EV.Code3
  UNION ALL
  SELECT 4 AS MatchedCode, * FROM ProcedureName WHERE CODE = EV.Code4
)
  PN

This unfortunatley doesn't seem to solve the issue of what happens if there are multiple procedures with the same code, IE Code1 = 10, code2=10. Which just results in only ONE row displaying. Admittedly the OR join caused the same issue, which I found out after testing this method. — Sean, Jun 26 '18 at 12:18
@Sean Yes, you get one row with multiple flag columns telling you which code(s) matched. If you only want to know which codes matched, you only need one row. If you ***need*** more than one row, you should make that clear in the question and look at Gordon's answer :) — MatBailie, Jun 26 '18 at 12:21

Gordon Linoff · Accepted Answer · 2018-06-27T02:24:20.587

1

I think apply does what you want:

select e.id, v.which, v.code
from Events e cross apply
     (values ('procedure1', code1), ('procedure2', code2), ('procedure3', code3), ('procedure4', code4)
     ) v(which, code)

If you want to filter out codes that are NULL, then add:

where v.code is null

edited Jun 27 '18 at 02:24

answered Jun 26 '18 at 11:47

Gordon Linoff

1,122,135
50
484
624

1

Yay for Gordon! Yay for cross apply-unpivot! Try this and learn this, it's good practice! – George Menoutis Jun 26 '18 at 11:52
I'm getting closer with using this technique, unfortunatly it will always show 4 lines, even if there is only 1 or two joins, which is not ideal :( – Sean Jun 26 '18 at 12:24
I've Solved the issue by wrapping the entire statement in another select, then filtering out any column that the procedure Description is NULL. Not the most elegant, but it works. So i'm marking this as the answer I went with :) Thanks. – Sean Jun 26 '18 at 12:36
@Sean - Then just turn your `LEFT JOIN` in to an `INNER JOIN` instead? It's the same behaviour. *(Do note that, either way, any rows in `Events` that don't have **any** matches in `ProcedureName` will be filtered out by such an approach.)* – MatBailie Jun 26 '18 at 12:41

Find What Column i Joined on when using OR

2 Answers2