Naqme Naqme - 1 year ago 94
MySQL Question

Join query on tables

I'm running 2 queries on a table, both of them are the same in "Where" clause, so I beleive they should return same values.

My first query is:

select B.NameDastgahID, B.ZarfiateHamleBar, B.SherkateSazande, B.BisimHF, B.KanalBisimKhodroyi, B.ShomarePelak, B1.NameDastgahTitle, B2.NameKhodroTitle, B3.NoeKhodroTitle, B4.KarbarieKhodroTitle, B5.ShahreKhodroTitle, B6.cheraghgardanValue, B7.bisimHFTitle
from dw.bohran_fct_etelaatenavegankhodroyi B
INNER JOIN dw.excel_dim_namedastgah B1 using (NameDastgahID)
INNER JOIN dw.excel_dim_namekhodro B2 using (NameKhodroID)
INNER JOIN dw.excel_dim_noekhodro B3 using (NoeNavganID)
INNER JOIN dw.excel_dim_karbariekhodro B4 using (KarbariID)
INNER JOIN dw.excel_dim_shahrekhodro B5 using (ShahrID)
INNER JOIN dw.excel_dim_cheraghgardan B6 using (CheraghGardan)
INNER JOIN dw.excel_dim_bisimhf B7 using (bisimHF)
where NameKhodroID = '630'

and it returns 61 rows.

The second query:

select *
from dw.bohran_fct_etelaatenavegankhodroyi
where NameKhodroID = '630'

this returns 122 rows.

The second query always returns double rows than the first, even when I change the condition. Any idea??

Answer Source

I'll suggest dropping one JOIN at a time, until you reach the one causing this.

This is not weird, its normal behavior , you are joining to 7 different tables, if any condition on all of this 7 tables will be incorrect , then the record would be filtered!