Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Not in Joining

Author  Topic 

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-01-15 : 10:11:21
How can I get those balance of list which not in the joining where condition?

select x.Number
,y.currencyCode,y.chargeAmount
into #withAAAchargecode
from tableA x
left join tableB y
on x.passengerid=y.passengerid
where chargecode = 'AAA'

As above, I only pull those Number which has chargecode = 'AAA'

How can I pull the remaining Number in tableA which haven't pull out?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-15 : 10:23:14
If chargecode is in tableB, insert that filter on chargecode in the JOIN instead of in the WHERE clause
select x.Number
,y.currencyCode,y.chargeAmount
into #withAAAchargecode
from tableA x
left join tableB y
on x.passengerid=y.passengerid
AND chargecode = 'AAA'
Go to Top of Page

theboyholty
Posting Yak Master

226 Posts

Posted - 2013-01-15 : 10:25:38
It depends largely on which table the chargecode field comes from.
I'm going to assume its coming from tableB.

select x.Number,y.currencyCode,y.chargeAmount
into #withAAAchargecode
from tableA x
left join tableB y on x.passengerid=y.passengerid and y.chargecode = 'AAA'
where y.passengerid is null


---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum
Go to Top of Page

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-01-15 : 10:39:15
owh..understood now.

Thanks!
Go to Top of Page
   

- Advertisement -