CODEWITHSUNDEEP
sql
JakeStid
JakeStid

Reputation: 1

Is there a way to use SUBQUERY for a cascading where statement?

I am attempting to attach some dummy data to use as a reference.

I am trying to pull a certain group of account numbers out of a pool of several hundred thousand. The pool is made up of lead accounts and follow accounts. The lead accounts start with 5 digits, and end with three zeroes. The follow accounts use the same 5 numbers, and changes the last three. There are generally 25-75 follow accounts for every lead account.

My goal is to pull all follow accounts from a certain group of lead accounts. The problem I am facing is that the follow accounts do not contain the implied criteria that I wish to filter out. The lead accounts have a color code, and all follow accounts are known to be under this color code. Unfortunately this is only given on the lead.

Here is what I have so far:

Select
    Account Number,
    Color Code
From
    Master.Table
Where
    LEFT(Account Number, 5) =
    (Select LEFT(Account Number, 5)
     From Master.Table
     Where Color Code IN ('Green', 'Magenta', 'Teal', 'Gray', 'Purple', 'Yellow', 'Beige'))

Upvotes: 0

Views: 66

Answers (2)

austin wernli
austin wernli

Reputation: 1801

You could also do something without a subquery, using joins

CREATE TABLE #tempTable (
    accountNumber NVARCHAR(20),
    colorCode NVARCHAR(20)
    )

INSERT INTO #tempTable (
    accountNumber,
    colorCode
    )
VALUES ('12345000','Magenta'),('12346000','Beige'),('12347000','Black'),
    ('12345123',''),('12346321',''),('12346387',''),('12347988','')

SELECT b.accountNumber, a.colorCode
FROM #tempTable a
LEFT JOIN #tempTable b
    ON LEFT(a.accountNumber, 5) = LEFT(b.accountNumber, 5)
        AND a.colorCode IN (
            'Green',
            'Magenta',
            'Teal',
            'Gray',
            'Purple',
            'Yellow',
            'Beige'
            )
WHERE RIGHT(b.accountNumber, 3) <> '000'

DROP TABLE #tempTable

This would return 

accountNumber   colorCode
12345123        Magenta
12346321        Beige
12346387        Beige

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Your code basically looks correct. I would make some minor modifications:

Select t.AccountNumber, t.ColorCode
From Master.Table t
Where left(t.AccountNumber, 5) in (
           Select left(t2.AccountNumber, 5)
           From Master.Table t2
           Where t2.ColorCode in ('Green', 'Magenta', 'Teal', 'Gray', 'Purple', 'Yellow', 'Beige') and
                 t2.AccountNumber like '%000'
          );

Notes:

  • This fixes the column names so they have no spaces.
  • This uses IN rather than =.
  • This ensures that only master accounts are considered in the subquery.

Upvotes: 1

Related Questions