SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 select associated accounts
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

richnb74
Starting Member

United Kingdom
4 Posts

Posted - 02/14/2014 :  09:57:21  Show Profile  Reply with Quote
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

Edited by - richnb74 on 02/14/2014 10:19:34

bitsmed
Constraint Violating Yak Guru

321 Posts

Posted - 02/14/2014 :  15:34:47  Show Profile  Reply with Quote
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

United Kingdom
4 Posts

Posted - 02/15/2014 :  16:24:56  Show Profile  Reply with Quote
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

United Kingdom
4 Posts

Posted - 02/17/2014 :  02:45:20  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 02/17/2014 :  05:24:25  Show Profile  Reply with Quote
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

United Kingdom
4 Posts

Posted - 02/17/2014 :  10:51:34  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 02/23/2014 :  10:22:17  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000