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.
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 = 93and lt.code = '1a'and l.balance >0I hope that makes sense!?regards in advancerich |
|
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' ) |
|
|
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. CheersRich |
|
|
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?regardsrich |
|
|
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?regardsrich
'try this and see if its what you're afterselect customernumber, accountsuffix, forename, surname, code, balance, journeynumberfrom(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 cntfrom 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)twhere cnt > 0 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 :) |
|
|
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, journeynumberfrom(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 cntfrom 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)twhere cnt > 0AND balance > 0 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|