Query Syntax

Hi All,

My table contains client number and related records. A client can have one or more than one account. I need records of the client which has only 1 account related to it. I am not able to build this query. Could you please help
jasmeen kaurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
can you provide some sample data?

and what database are you using?
0
Pawan KumarDatabase ExpertCommented:
Below query will give you proper output you need.  For this I am using count(*) Over (Partition by client order by account) so that we can count how many accounts each client has

SELECT * FROM 
(
SELECT * , COUNT(*) OVER (PARTITION BY client order by account) cnt
FROM yourtable 
)r 
WHERE cnt = 1

Open in new window

0
jasmeen kaurAuthor Commented:
It is oracle Db. Below is the sample data. I need client which has only one account, here it is client 2 , so I need the 3 row of this table

Client Number     Account Number     Balance
C1                           A123                          100
C1                           A546                          50
C2                           A65456                      30
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Pawan KumarDatabase ExpertCommented:
Please try this -

SELECT y.* FROM 
(
	SELECT [Client Number] FROM 
	(
		SELECT [Client Number] , COUNT(*) cnt
		FROM yourtable 
		GROUP BY [Client Number]
	)r 
	WHERE cnt = 1
)o INNER JOIN yourtable y ON O.[Client Number] = y.[Client Number]

Open in new window


Output

/*------------------------
OUTPUT
------------------------*/
Client Number Account Number Balance
------------- -------------- -----------
C2            A65456         30

(1 row(s) affected)

Open in new window

0
Pawan KumarDatabase ExpertCommented:
For ORACLE  please use below

You can adjust the column names.

WITH CTE AS 
(
    SELECT 'C1' c ,'A123' a , 100 b
    FROM DUAL  UNION ALL
    SELECT 'C1','A546',50  FROM DUAL UNION ALL
    SELECT 'C2','A65456',30  FROM DUAL
)
,CTE1 AS
(
    SELECT c FROM 
    (SELECT c , COUNT(*) cnt
    FROM CTE 
    GROUP BY c) WHERE cnt = 1
)
SELECT k1.* FROM CTE1 k INNER JOIN CTE k1 ON k1.c = k.c

Open in new window


OUTPUT

 	C	A	B
1	C2	A65456	30

Open in new window

0
Ryan ChongCommented:
a similar solution using Group By with Having clause.

create table mytable (
    Client_Number varchar2(4000),
    Account_Number varchar2(4000),
    Balance number
);
/

insert into mytable (Client_Number, Account_Number, Balance)
select 'C1', 'A123', 100 from dual union all
select 'C1', 'A546', 50 from dual union all
select 'C2', 'A65456', 30 from dual;
/

select a.* from mytable a
inner join (
select Client_Number from mytable group by Client_Number having count(*) = 1
) b on a.Client_Number = b.Client_Number

Open in new window

0
Pawan KumarDatabase ExpertCommented:
another simple and tested method is given below -

Explanation - Just count how many accounts you have per client and take client who has only 1 account using -  COUNT(*) OVER(PARTITION BY c) as cnt

WITH CTE AS 
(
    SELECT 'C1' c ,'A123' a , 100 b
    FROM DUAL  UNION ALL
    SELECT 'C1','A546',50  FROM DUAL UNION ALL
    SELECT 'C2','A65456',30  FROM DUAL
)
SELECT c clientNumber ,a accountNumber , b Balance
FROM 
(
    SELECT c,b,a , COUNT(*) OVER(PARTITION BY c) as cnt FROM CTE

)WHERE cnt = 1 

Open in new window


Output

 	CLIENTNUMBER	ACCOUNTNUMBER	BALANCE
1	C2	A65456	30

Open in new window

0
jasmeen kaurAuthor Commented:
@Pawan: Thanks for your help.
I have one issue: in the WITH CTE clause, how will I select the data? c1, A123 this is data...should I give column names here?
0
Pawan KumarDatabase ExpertCommented:
can you give me your query. Will modify that.

You can use SELECT * FROM CTE

or

SELECT col1, col2 ....etc from CTE
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
select * from yourtable where clientnumber in
(select clientnumber from
 (select clientnumber, count(*) from yourtable group by clientnumber having count(*) = 1)
)
1
Geert GOracle dbaCommented:
you don't need to supply the count(*) in the column names ...

select * from yourtable where clientnumber in
 (select clientnumber from yourtable group by clientnumber having count(*) = 1)

Open in new window

1
jasmeen kaurAuthor Commented:
Thanks for your help guys. My problem solved
0
Pawan KumarDatabase ExpertCommented:
welcome. glad to help as always.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.