2

When making a left join with another SQL table some of my left tables rows are being duplicated and joined with multiple right table rows, since in my usage this means these are ambiguous rows I need to be able to filter rows which have the same value as each other.

ie

Select Table_1.Id, Table_1.Value, Table_2.Id, Table_2.Value
From Table_1 Left Join Table_2 on Table_1.Value = Table_2.Value

Resulting in:

Table_1.Id,     Table_1.Value,  Table_2.Id,     Table_2.Value
1               T               2               T
2               G               3               G
2               G               5               G
3               K               1               K
4               M               4               M

However I want to create a query that results in:

Table_1.Id,     Table_1.Value,  Table_2.Id,     Table_2.Value
1               T               2               T
3               K               1               K
4               M               4               M

Where Since Row 2 had joined with multiple rows in Table_2 it is rejected from the query.

I've Had a look at Finding duplicate values in a SQL table and have been able to use it to find duplicates within Table_1 but not rows that have been connected to multiple Table_2 rows. Any help would be greatly appreciated.

Excalibur
  • 33
  • 1
  • 7
  • Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy Jun 26 '20 at 02:04
  • I meant to cut & paste the SQL version. I will. But you will find many Qs&As answering this if you follow my 1st comment. Clearly we can expect that, you are not the first person to face this. ... [SQL query that gives distinct results that match multiple columns](https://stackoverflow.com/q/1202668/3404097) – philipxy Jun 26 '20 at 04:28

1 Answers1

2

You can use count() as a window function:

Select t1.Id, t1.Value, t2.Id, t2.Value
From Table_1 Join
     (select t2.*,
             count(*) over (partition by t2.value) as cnt
      from Table_2 t2
     ) t2
     on t2.Value = t1.Value
where t2.cnt = 1;

Based on your sample data, left join does not seem appropriate.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786