multiple inner joins in bigquery result in duplicated rows

Question

Im tryin to join 6 tables in bigquery named T0, T1, T2, T3, T4, T5 The tables result im interested are T0 and T1 after query this tables I got 43 matches

SELECT  
        T1.F1, 
        T0.F2, 
        T0.F3, 
        T0.F4, 
        T1.F5,
        T1.F6,
        T1.F7,
        T1.F8
        T0.F9
        FROM `TABLE0` T0
        INNER JOIN `TABLE1` T1 on T1.F1= T0.F1
        WHERE T0.F1 = "010001476713" 
        AND T0.F2 = T1.F2
        ORDER BY T0.F4

But when I run this with multiple INNER JOIN I got 800 results not the 43, results are duplicated

SELECT
T2.F11,
T3.F15,
T2.F12,
T3.F16,
T3.F17,
T1.F1, 
T2.F13,
T3.F17,
T5.F18,
T5.F19,
T5.F20,
T2.F14,
T0.F9,
T1.F10,
T4.F3,
T4.F21,
T4.F22,
T0.F2, 
T3.F23,
T0.F3, 
T0.F4, 
T1.F5,
T1.F6,
T1.F7,
T1.F8
FROM `TABLE0` T0
INNER JOIN `TABLE1` T1 ON T1.F1= T0.F1
INNER JOIN `TABLE3`  T3 ON T3.F1=T1.F1
INNER JOIN `TABLE2` T2 ON T2.F24 = T3.F24
INNER JOIN `TABLE4` T4 ON T4.F3 = T0.F3
INNER JOIN `TABLE5` as T5 ON T5.F1=T0.F1
WHERE T0.F1 = "010001476713" 
AND T0.F2 = T1.F2
ORDER BY T0.F4

A [mcve] is a great start when asking for SQL assistance. Note that additional JOIN's typically give more rows returned, depending on table data and type of relationships. — jarlh, Aug 13 '22 at 19:18
Does this answer your question? [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result) — philipxy, Aug 13 '22 at 21:40
Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, Aug 13 '22 at 21:43
Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] — philipxy, Aug 13 '22 at 21:44

jenstreetman · Answer 1 · 2022-08-15T03:44:41.340

When I get duplicate rows, I solve it like this:

You get 43 results on your inner join of table T0 & T1. So far so good.

Now comment out everything related to table T2, T4, & T5 (I've placed the commas at the beginning of the row for easier commenting out) like this

SELECT
--T2.F11,
T3.F15
--,T2.F12
,T3.F16
,T3.F17
,T1.F1 
--,T2.F13
,T3.F17
--,T5.F18
--,T5.F19
--,T5.F20
--,T2.F14
,T0.F9
,T1.F10
--,T4.F3
--,T4.F21
--,T4.F22
,T0.F2 
,T3.F23
,T0.F3 
,T0.F4 
,T1.F5
,T1.F6
,T1.F7
,T1.F8
FROM `TABLE0` T0
INNER JOIN `TABLE1` T1 ON T1.F1= T0.F1 and T0.F2 = T1.F2
INNER JOIN `TABLE3`  T3 ON T3.F1=T1.F1
--INNER JOIN `TABLE2` T2 ON T2.F24 = T3.F24
--INNER JOIN `TABLE4` T4 ON T4.F3 = T0.F3
--INNER JOIN `TABLE5` as T5 ON T5.F1=T0.F1
WHERE T0.F1 = "010001476713" 
ORDER BY T0.F4

I've moved the and T0.F2 = T1.F2 from the where to on in the inner join. When you run this query, do you still get 43 rows, or more? If more, you need to figure out what it is double matching on, and add that to your on statement of it really is a 1-1 relationship, or perhaps group the results if you don't want multiple matches. You may need to comment out your select statement and select all to really figure it out, like this:

SELECT *
/*
--T2.F11,
T3.F15
--,T2.F12
,T3.F16
,T3.F17
,T1.F1 
--,T2.F13
,T3.F17
--,T5.F18
--,T5.F19
--,T5.F20
--,T2.F14
,T0.F9
,T1.F10
--,T4.F3
--,T4.F21
--,T4.F22
,T0.F2 
,T3.F23
,T0.F3 
,T0.F4 
,T1.F5
,T1.F6
,T1.F7
,T1.F8
*/
FROM `TABLE0` T0
INNER JOIN `TABLE1` T1 ON T1.F1= T0.F1 and T0.F2 = T1.F2
INNER JOIN `TABLE3`  T3 ON T3.F1=T1.F1
--INNER JOIN `TABLE2` T2 ON T2.F24 = T3.F24
--INNER JOIN `TABLE4` T4 ON T4.F3 = T0.F3
--INNER JOIN `TABLE5` as T5 ON T5.F1=T0.F1
WHERE T0.F1 = "010001476713" 
ORDER BY T0.F4

Once you figure out what rows are causing the duplication, you either group the results or add an 'and' statement to the on clause to make it a 1-1, and then move on. You then uncomment the parts of the query related to T2 and do the same thing, then T4 and then T5. If you send me the results of the query above, I can help you figure out what your on clause needs to be to keep it from duplicating.

score 1 · Answer 2 · answered Aug 16 '22 at 15:54

thank you @jenstretman, I find table 4 to be duplicating matches by using a foreign Key with non-primary Key creating duplicates, the solution was to use a DISTINCT to only select specifically matched rows.

SELECT DISTINCT
T2.F11,
T3.F15,
T2.F12,
T3.F16,
T3.F17,
T1.F1, 
T2.F13,
T3.F17,
T5.F18,
T5.F19,
T5.F20,
T2.F14,
T0.F9,
T1.F10,
T4.F3,
T4.F21,
T4.F22,
T0.F2, 
T3.F23,
T0.F3, 
T0.F4, 
T1.F5,
T1.F6,
T1.F7,
T1.F8
FROM `TABLE0` T0
INNER JOIN `TABLE1` T1 ON T1.F1= T0.F1
INNER JOIN `TABLE3`  T3 ON T3.F1=T1.F1
INNER JOIN `TABLE2` T2 ON T2.F24 = T3.F24
INNER JOIN (SELECT DISTINCT T4.F3, T4.F21, T4.F22, FROM `TABLE4` T4)T4 ON T4.F3 = T0.F3
INNER JOIN `TABLE5` as T5 ON T5.F1=T0.F1
WHERE T0.F1 = "010001476713" 
AND T0.F2 = T1.F2
ORDER BY T0.F4

multiple inner joins in bigquery result in duplicated rows

2 Answers2