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
 select associated accounts

Author  Topic 

richnb74
Starting Member

4 Posts

Posted - 2014-02-14 : 09:57:21
Hi, I am quite new to this so please be patient. Thanks.

I have four tables (all inner joined) and currently they give me the results i need. However, my boss has now asked me to return all associated accounts as well.
let me explain further....

I am currently pulling data from the four tables to make up my results table, and the returned results are based on the loan types in my loans tables having a loan type of '1A'
So if the loan type is 1A I get a result.

However, Mr Smith (for example) may have three loans but only one of them is type '1A'. The other two might be type '5H' and '2'.

What I need to be able to do is return all the associated accounts of any customer that has a type '1A' loan.

this is my code:
Select c.customernumber, l.accountsuffix, c.forename, c.surname, lt.code, l.balance, j.journeynumber from customers c
inner join loanagreements l on c.customerid = l.customerid
inner join loantypes lt on l.loantypeid = lt.loantypeid
inner join journeys j on c.journeyid = j.journeyid
Where j.journeynumber = 93
and lt.code = '1a'
and l.balance >0

I hope that makes sense!?

regards in advance

rich

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-02-14 : 15:34:47
Try this:
select c.customernumber
,l.accountsuffix
,c.forename
,c.surname
,lt.code
,l.balance
,j.journeynumber
from customers c
inner join loanagreements l
on c.customerid=l.customerid
inner join loantypes lt
on l.loantypeid=lt.loantypeid
inner join journeys j
on c.journeyid=j.journeyid
Where j.journeynumber=93
and l.balance>0
and exists (select 1
from loantypes lt1a
where lt1a.loantypeid=l.loantypeid
and lt1a.code='1a'
)
Go to Top of Page

richnb74
Starting Member

4 Posts

Posted - 2014-02-15 : 16:24:56
Thanks bitsmed. I will try it out on Monday at work and let you know how I get on.

Cheers

Rich
Go to Top of Page

richnb74
Starting Member

4 Posts

Posted - 2014-02-17 : 02:45:20
Hi bitsmed, I copied and pasted your code exactly as you had written it but it is still giving me the same results as my original query.

thanks for trying though, much appreciated.
any more ideas?

regards

rich
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-17 : 05:24:25
quote:
Originally posted by richnb74

Hi bitsmed, I copied and pasted your code exactly as you had written it but it is still giving me the same results as my original query.

thanks for trying though, much appreciated.
any more ideas?

regards

rich


'
try this and see if its what you're after

select customernumber, accountsuffix, forename, surname, code, balance, journeynumber
from
(
Select c.customernumber, l.accountsuffix, c.forename, c.surname, lt.code, l.balance, j.journeynumber,
sum(case when lt.code = '1a' and l.balance >0 then 1 else 0 end) over (partition by c.customernumber) as cnt
from customers c
inner join loanagreements l on c.customerid = l.customerid
inner join loantypes lt on l.loantypeid = lt.loantypeid
inner join journeys j on c.journeyid = j.journeyid
Where j.journeynumber = 93
)t
where cnt > 0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

richnb74
Starting Member

4 Posts

Posted - 2014-02-17 : 10:51:34
hi Visakh16,
that looks pretty much like what I'm after, however it has returned accounts that have a nil balance, which would mean those accounts are finished with and don't want including in the results.

many thanks though, it's almost there.
I just need to study it now to see what you've done :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-23 : 10:22:17
so do you mean this?


select customernumber, accountsuffix, forename, surname, code, balance, journeynumber
from
(
Select c.customernumber, l.accountsuffix, c.forename, c.surname, lt.code, l.balance, j.journeynumber,
sum(case when lt.code = '1a' and l.balance >0 then 1 else 0 end) over (partition by c.customernumber) as cnt
from customers c
inner join loanagreements l on c.customerid = l.customerid
inner join loantypes lt on l.loantypeid = lt.loantypeid
inner join journeys j on c.journeyid = j.journeyid
Where j.journeynumber = 93
)t
where cnt > 0
AND balance > 0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -