How do I join these two tables? (SQL)

[cbrunny]

I have two tables with similar data. I want them to join on three elements:

t1.A = t2.A
t1.B = t2.B
t1.C = t2.C

Simple enough.

Except:

- t1 has 564 cases. t2 has several thousand. For each case of A, B, and C in t1, there is between 1 and n cases in t2.

- There are multiple rows in t1 where A, B, and C are equal, and differ on a different element - say D. D is wholly distinct.

- t2 has multiple rows where A, B, and C are equal and differ on D as well. D is wholly distinct.

- t1.D is mutually exclusive from t2.D. There is no overlap of any kind.

- the output needs to have one row per combination, where all elements of t1 are prior to all elements of t2 moving left to right (the easy part) AND I only want one match per entry of t1 AND there can be no duplicates of t2.D (the hard part)

Help?

 

[Lotos]

SELECT DISTINCT t1.*, t2.*
FROM t1
LEFT JOIN t2 ON(t1.A = t2.A AND t1.B = t2.B AND t1.C = t2.C)

something like this maybe? I'm pretty bad at SQL theory. I usually tend to just write a query, run it, and tweak as needed.

 

[cbrunny]

I believe that would give me duplicate entries of t2. the distinct in the select would only act on the row as a whole.

 

[PowerYoga]

So D can be distinct, so are you ok with something like T1.A, T1.B, T1.C, T2.D1; T1.A, T1.B, T1.C, T2.D2, etc etc?

Can't you just use a group by and tweak your select to return the columns you want? Any distinct rows (where D differs) will be returned.

select T1.A, T1.B, T1.C, T2.D
from T1
JOIN T2
ON T1.A = T2.A AND T1.B = T2.B AND T1.C = T2.C
group by T1.A

or something similar.

 

[cbrunny]

I ended up adding a running total within each table of the different combinations of A B and C. Since t2 was guaranteed to have more than t1, I added a join between t1.RT and t2.RT. this produced the desired outcome. No duplicate t2.D.