Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to rewrite this correlated subquery using joins

Posted on 2012-09-15
3
Medium Priority
?
873 Views
Last Modified: 2012-09-15
I have a correlates subquery. The query selects all bank customers with more than one bank account.

SELECT c.cust_id,c.cust_type_cd,c.city FROM customer c
WHERE 2 = (SELECT COUNT(*)
FROM account a WHERE a.cust_id = c.cust_id)

So my question is, how can I rewrite this query, so that is uses only joins?
0
Comment
Question by:brgdotnet
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
Thomasian earned 720 total points
ID: 38401876
Assuming cust_id is unique on table customer:
SELECT c.cust_id, c.cust_type_cd, c.city
FROM customer c INNER JOIN
     account a ON a.cust_id = c.cust_id
GROUP BY c.cust_id, c.cust_type_cd, c.city
HAVING COUNT(*) = 2

Open in new window

0
 

Assisted Solution

by:Jamieo1
Jamieo1 earned 120 total points
ID: 38402177
SELECT Distinct  c.cust_id,c.cust_type_cd,c.city
FROM customer c
JOIN  account a ON a.cust_id = c.cust_id
GROUP BY c.cust_id,c.cust_type_cd,c.city
HAVING COUNT(*) =2

i think you need distinct to eliminate dupes.
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 38402606
Jamieo1,

There won't be any duplicates after using GROUP BY...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question