How to rewrite this correlated subquery using joins

Posted on 2012-09-15
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, FROM customer c
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?
Question by:brgdotnet
    LVL 22

    Accepted Solution

    Assuming cust_id is unique on table customer:
    SELECT c.cust_id, c.cust_type_cd,
    FROM customer c INNER JOIN
         account a ON a.cust_id = c.cust_id
    GROUP BY c.cust_id, c.cust_type_cd,
    HAVING COUNT(*) = 2

    Open in new window


    Assisted Solution

    SELECT Distinct  c.cust_id,c.cust_type_cd,
    FROM customer c
    JOIN  account a ON a.cust_id = c.cust_id
    GROUP BY c.cust_id,c.cust_type_cd,
    HAVING COUNT(*) =2

    i think you need distinct to eliminate dupes.
    LVL 22

    Expert Comment


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

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now