Help with a query - SQL Server Forums

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

Help with a query

Author

Topic

help_needed
Starting Member

12 Posts

Posted - 2009-03-17 : 12:06:24

Hi

I am struggling with a query and would be grateful if anyone can help me

The problem is that some clients who have more than one lender in the table have the same CaseID but the Loan column goes from 1 to 2.
I am trying to select all of the records that only have 1 loan and where they have more than one to ignore the record completly. A sample of the table is below

ID CaseID Loan Lender
45184 374946 1 Nat West Retail Banking Services
45185 374946 2 First National Bank Plc
45189 374961 1 Abbey National Mortgage Financ
45190 374968 1 Mortgage Corporation
45191 374971 1 Barclays Home Finance
45192 374981 1 I Group Mortgages Ltd
45205 375020 1 I Group Mortgages Ltd
45206 375020 2 Blackhorse Finance Ltd
45207 375018 1 I Group Mortgages Ltd
45210 374982 2 citi financial
45211 374982 2 welcome finance
45217 375040 1 Abbey National Plc - Bradford
45220 375043 1 citi financial europe plc
45221 375043 2 welcome finance

from this data if I was just selecting the CaseID I would want the results of

374961
374968
374971
374981
375018
375040

and to ignore

374946
375020
374982
375043

Hope I have explained this clearly enough

Thanks

pootle_flump

1064 Posts

Posted - 2009-03-17 : 12:12:01

[code]SELECT CaseID
FROM myT
GROUP BY CaseID
HAVING COUNT(*) = 1[/code]???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 13:12:42

if sql 2005 then


SELECT ID,	CaseID,	Loan,	Lender
FROM
(
SELECT ID,	CaseID,	Loan,	Lender,COUNT(Loan) OVER (PARTITION BY CaseID) AS Occurance
FROM TABLE
)t
WHERE Occurance=1

help_needed
Starting Member

12 Posts

Posted - 2009-03-17 : 14:15:14

Thanks a lot for all the help - all sorted now

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources