CODEWITHSUNDEEP
sqlsql-serverleft-joinuniquedistinct
steventnorris
steventnorris

Reputation: 5896

SQL Unique Records with multiple joins

I have three tables I need to join. The first two are easy and based on the same key. A simple outer join will work. The third is tricky. It has a similar key but must be matched on the left 14 of one key. This produces duplicates which I do not want. Essentially, I want the TOP 1 FROM Table3 that matches the IDs correctly.

Using T-SQL.

The tables are large and complicated, so I've outlined below the a simple example of the tables with sample data and the query I'm running now.

Table1

ID1          ID2                 Field1    Field2    Field3    Field4
0000000000   00000000000000a     Info1     Info2     Info3     Info4
2222222222   11111111111111b     Info1     Info2     Info3     Info4
1111111111   22222222222222c     Info1     Info2     Info3     Info4

Table2

ID1          ID2                 Field1    Field2  
0000000000   00000000000000a     Info5     Info6  
2222222222   11111111111111b     Info5     Info6  
1111111111   22222222222222c     Info5     Info6

Table

ID1          ID2                 Field1    
0000000000   00000000000000a     Info7 
0000000000   00000000000000b     Info7  
0000000000   00000000000000c     Info7  
2222222222   11111111111111b     Info7  
2222222222   11111111111111d     Info7  
1111111111   22222222222222c     Info7

Query

SELECT
Table1.ID1,
Table1.ID2,
Table1.Field1,
Table1.Field2,
Table1.Field3,
Table1.Field4,
Table2.Field1,
Table2.Field2,
Table3.Field1
FROM Table1
LEFT JOIN Table2
ON Table1.ID1=Table2.ID2 AND Table1.ID2=Table2.ID2
LEFT JOIN Table3
ON Table1.ID1=Table3.ID2 AND LEFT(Table1.ID2,14)=LEFT(Table3.ID2,14)

The response I get is

0000000000 00000000000000a Info1 Info2 Info3 Info4 Info5 Info6 Info7
0000000000 00000000000000a Info1 Info2 Info3 Info4 Info5 Info6 Info7
0000000000 00000000000000a Info1 Info2 Info3 Info4 Info5 Info6 Info7
2222222222 11111111111111b Info1 Info2 Info3 Info4 Info5 Info6 Info7
2222222222 11111111111111b Info1 Info2 Info3 Info4 Info5 Info6 Info7
1111111111 22222222222222c Info1 Info2 Info3 Info4 Info5 Info6 Info7

What I want is

0000000000 00000000000000a Info1 Info2 Info3 Info4 Info5 Info6 Info7
2222222222 11111111111111b Info1 Info2 Info3 Info4 Info5 Info6 Info7
1111111111 22222222222222c Info1 Info2 Info3 Info4 Info5 Info6 Info7

Upvotes: 1

Views: 1658

Answers (2)

Taryn
Taryn

Reputation: 247850

Unless I am missing something in the requirements, if you only want to return one record for each ID1 in Table3 you should be able to do something like this:

SELECT
    t1.ID1,
    t1.ID2,
    t1.Field1,
    t1.Field2,
    t1.Field3,
    t1.Field4,
    t2.Field1,
    t2.Field2,
    tb3.Field1
FROM Table1 t1
LEFT JOIN Table2 t2
    ON t1.ID1=t2.ID1 
    AND t1.ID2=t2.ID2
LEFT JOIN
(
    select min(id2) minid2, id1
    from Table3
    group by id1
) t3
    ON t1.ID1=t3.id1 
    AND LEFT(t1.ID2,14)=LEFT(t3.minid2,14)
left join table3 tb3
    on t3.minid2 = tb3.id2
    and t3.id1 = tb3.id1

Upvotes: 0

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

I will take a guess and say you use mssql, (using the top and stuff) so this will work fine if you use 2005+

SELECT
  Table1.ID1,
  Table1.ID2,
  Table1.Field1,
  Table1.Field2,
  Table1.Field3,
  Table1.Field4,
  Table2.Field1,
  Table2.Field2,
  t3.Field1
FROM Table1
LEFT JOIN Table2
  ON Table1.ID1=Table2.ID2 AND Table1.ID2=Table2.ID2
outer apply (select top 1 * from table3 where LEFT(Table1.ID2,14)=LEFT(Table3.ID2,14)) t3

Upvotes: 2

Related Questions