LEFT JOIN Duplicating Rows

Question

Table 1 has the join field (fieldY) duplicated many times within this table although every row in totality is unique.

When I try to run a left join I am getting 20x more rows than expected. I have tried to use solutions this post with no luck.

My expectation is that the join would yield exactly as many rows as table1 without the join. The join would just bring in one more column (fieldX)

Any ideas?

SELECT 
    table1.*, table2.fieldZ
FROM
    table1
LEFT JOIN
    table2
ON
    table2.fieldX = table1.fieldY
WHERE
    criteria1 = '01/01/2019'
AND
    criteria2 > '0'
ORDER BY
    criteria2

In below photo:

fieldz = Routing #
fieldX = Bank Account # (From table2)
fieldY = Bank Account # (From table1)

*This can be joined by student ID or Bank Account #, but the problem is the same regardless because both Student ID & Bank Account # appear multiple times in Table 1`

For every table1.fieldY there will be as many rows as there are matching table2.fieldX from table2, or 1 if there is no matching. — forpas, Jul 02 '19 at 22:55
If there are multiple rows in table2 with the same fieldx then that would be why you are seeing duplicates. — Jonathan Porter, Jul 02 '19 at 22:55
You can start with this test. This shouldn't return any rows. If it return any row, it will show you which fieldx value has duplicates on table2. If you have duplicates on table2, when you join it with table1 it will render duplicate rows of table1 (one for each relative value on table2). SELECT fieldx FROM table2 GROUP BY fieldx HAVING COUNT(1) > 1; — Marcelo Myara, Jul 02 '19 at 22:58
There was a typo in my post that may change this (importing `fieldz` and joining on `fieldx` & `fieldy`) @MarceloMyara. Sorry, just started learning SQL **yesterday** lol — urdearboy, Jul 02 '19 at 23:27
This does not make any difference. The condition `table2.fieldX = table1.fieldY` joins the tables. So there will be as many rows as there are matches between these columns. — forpas, Jul 02 '19 at 23:29
Post sample data, because it is hard to understand what you mean by duplicates. — forpas, Jul 02 '19 at 23:31
Fine, then for each row in table1 the column Car will match 1 or more rows of table2 on the column fieldX when the 2 columns are equal. This is how a join works. — forpas, Jul 02 '19 at 23:36
@forpas I updated with an example of inputs and desired output — urdearboy, Jul 02 '19 at 23:45
See my answer and check if you are doing something different — forpas, Jul 03 '19 at 00:03

score 0 · Answer 1 · answered Jul 02 '19 at 23:24

0

Table2 must have Table 1's key across multiple records. Needs to be 1:1 to achieve what you are describing.

answered Jul 02 '19 at 23:24

alexherm

1,362
2
18
31

Actually it doesn't need to be 1:1, it just needs to be unique to table2. – Bryant Jul 02 '19 at 23:49

score 0 · Answer 2 · answered Jul 03 '19 at 00:03

0

With this query:

select t1.*, t2.routing 
from table1 t1 left join table2 t2
on t2.studentid = t1.studentid

or this:

select t1.*, t2.routing 
from table1 t1 left join table2 t2
on t2.bankaccount = t1.bankaccount

you get these results:

> studentid | bankaccount |  gpa | semester | routing
> --------: | ----------: | ---: | :------- | :------
>         1 |      123456 |      | Fall     | abc    
>         1 |      123456 |      | Spring   | abc    
>         1 |      123456 |      | Summer   | abc    
>         2 |      456789 |      | Fall     | def    
>         2 |      456789 |      | Spring   | def    
>         2 |      456789 |      | Summer   | def    
>         3 |      321654 |      | Fall     | ghi    
>         3 |      321654 |      | Spring   | ghi    
>         3 |      321654 |      | Summer   | ghi

You can check the demo.

answered Jul 03 '19 at 00:03

forpas

160,666
10
38
76

So is it the other arguments I have that are messing this up for me? – urdearboy Jul 03 '19 at 01:51
A WHERE clause does not increase the rows of a query. – forpas Jul 03 '19 at 06:47
You tagged only sql. Which dbms or tool are you using? – forpas Jul 03 '19 at 08:12
Presto which doesn’t appear to have a tag. I’m told it’s similar to raptor? Not sure though - I literally started SQL this week. I can’t figure this out for the life of me – urdearboy Jul 03 '19 at 23:31
I'm sure that either the data you are querying is different than what you think, or you are doing something wrong. Post the results you get also. – forpas Jul 04 '19 at 07:57

LEFT JOIN Duplicating Rows

2 Answers2