stumped by a LEFT JOIN outcome

Question

stumped by a LEFT JOIN outcome

polkadot

SSChampion

Points: 13664
More actions
February 2, 2020 at 11:40 pm
Go to Answer
#3722438
I want only records in Table A that have disabled= 0 LEFT JOINed to Table B.
Below query returns a record from table A that I don't even want considered when joining to table B. It returns a row even though it's Disabled Flag = 1.
```
 
insert into #SOURCETABLE (ID , DisableFlag)
values (3,1)
 
insert into #TARGETTABLE (ID, Statecode)
values (3,1)

select 'SOURCETABLE' as tablename, * 
from #SOURCETABLE 

select 'TARGETTABLE' as tablename, * 
from #TARGETTABLE 

Select  A_ID  = a.ID  
   ,a.DisableFlag
   ,B_ID  = b.ID  
   ,b.statecode
From #SOURCETABLE A
       Left Join  #TARGETTABLE B
              on a.ID = b.ID
              AND a.DisableFlag = 0
```
Returns
ID DisableFlag ID statecode
3 1 NULL NULL
- This topic was modified 4 years, 3 months ago by polkadot.
- This topic was modified 4 years, 3 months ago by polkadot.
- This topic was modified 4 years, 3 months ago by polkadot.
- This topic was modified 4 years, 3 months ago by polkadot.
- This topic was modified 4 years, 3 months ago by polkadot.
- This topic was modified 4 years, 3 months ago by polkadot.
--Quote me
frederico_fonseca

SSCoach

Points: 16027
More actions
February 3, 2020 at 12:29 am
Answer
#3722450

you don't have any filtering on #SOURCETABLE - you only filter on the join, so all records from #SOURCETABLE will be selected.
and the only record you have on the sample data only has disableflag = 1 so that is what is showing, and the join is returning null as on the join you only look for where #SOURCETABLE disableflag = 0

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

polkadot SSChampion Points: 13664 More actions · Answer 1

So left join just brings over everything *no matter what* filters I put on the A table on the AND?

I see that appears how it works. I added another row to A table and then the AND filter controls which of the rows the bottom table will attempt to align to, but everything from LEFT TABLE is returned, no matter what

drop table if exists #SOURCETABLE
create table #SOURCETABLE (
ID int,
DisableFlag int
)
  
drop table if exists #TARGETTABLE
create table #TARGETTABLE (
ID int,
Statecode int
)
 
insert into #SOURCETABLE (ID , DisableFlag)
values (3,1),(2,0)
 
insert into #TARGETTABLE (ID, Statecode)
values (3,1), (2,0)

select 'SOURCETABLE' as tablename, * 
from #SOURCETABLE 

select 'TARGETTABLE' as tablename, * 
from #TARGETTABLE 


Select  A_ID  = a.ID  
   ,a.DisableFlag
   ,B_ID  = b.ID  
   ,b.statecode
From #SOURCETABLE A
       Left Join  #TARGETTABLE B
              on a.ID = b.ID
             and  a.DisableFlag = 1
             and b.Statecode = 1

This reply was modified 4 years, 3 months ago by polkadot.
This reply was modified 4 years, 3 months ago by polkadot.
This reply was modified 4 years, 3 months ago by polkadot.

--Quote me

frederico_fonseca SSCoach Points: 16027 More actions · Answer 2

you need to understand how filtering can be done - and the order it happens. and the difference between being a inner or a outer join

Join - indicates how the join of the tables happen - if inner join it has same result as a where clause
Where - indicates filtering of the result set - if incorrectly used with a outer join it can make the outer join behave as an inner join
Having - indicates filtering of a group by

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

if i'm reading this correctly - just turn it into an inner join

and for code clarity move the disabledflag=0 into the where clause... it has no place in the join

MVDBA

m-scally SSC Journeyman Points: 99 More actions · Answer 4

m-scally

SSC Journeyman

Points: 99

February 6, 2020 at 11:35 am

#3723557

+1 Agree