quote:

---

Pardon my ignorance and not being that savvy with writing queries, but I have ran into a problem, where I'd need you all's expertise.
I need to write a query where the output would provide me results based on two POSSIBLE columns from two different tables. Here is an example.
I have tables called Table1 and Table2. Both of these tables hold two columns that could possibly be used as join ids.

For example in Table1 I have a column called PrimaryMatchID. The PrimaryMatchID in table1 could have integers, 0's or NULLs, then there is SecondaryMatchID which holds Long Ints only, no nulls, nor 0's. In Table2 I also have the same exact two columns, however if one of them holds a value, the other one is set to 0. For example, if one row of Table2's PrimaryMatchID holds an id, then SecondaryMatchID in Table2 holds a 0 and vice versa. So Table two would looks something like this:

Table2

PrimaryMatchID | SecondaryMatchID

23234234 | 0
0 | 433
0 | 567
4432424 | 0



So I hope this makes it clear. I know the design is bad and this is something I have inhereted. However, I do have the flexibility to redesign the Table2. If not then, how can I go about writing the joins to get the viable results. Any help would truly appreciated. Thank you.

---

quote:

---

declare @table1 table (PMI int, SMI int)

insert into @table1 (PMI,SMI)
select 234, 23432423 union
select NULL,32432432 union
select 0,12123321 union
select 3456,42134242 union
select 37,24

declare @table2 table(PMI int, SMI int)

insert into @table2 (PMI,SMI)
select 234,0 union
select 0,32432432 union
select 0,12123321 union
select 3456,0 union
select 24,0


SELECT
t2.*
FROM
@table1 t1
JOIN
@table2 t2
ON
Coalesce(NullIf(t1.pmi,0),NullIf(t1.smi,0),-1) = Coalesce(NullIf(t2.pmi,0),NullIf(t2.smi,0),-2) OR
Coalesce(NullIf(t1.smi,0),NullIf(t1.pmi,0),-1) = Coalesce(NullIf(t2.pmi,0),NullIf(t2.smi,0),-2)

---

quote:

---

SELECT t2.*
FROM @table1 AS t1
INNER JOIN @table2 AS t2 ON t2.PMI + t2.SMI IN (t1.PMI, t1.SMI)

---