CODEWITHSUNDEEP
sqlsql-server
Damien
Damien

Reputation: 4319

Select column with Case and using WHERE on that column

This is a little complicated but I simplified it as much as I can. I have a contacts table that contains first name last name, etc. It also contains 2 columns indicating the group (src) the contact is associated with and it's id in that group (uID). I'm trying to do a contact search. So if I'm looking for mike, it's simple enough

... WHERE fname LIKE '%mik%'.

That returns all mikes. What I'm trying to do now is do a search based on the group or name. So if I want all the contacts associated with groupA; I would go

.. WHERE fname LIKE '%mik%' OR code LIKE '%groupA%'.

Unfortunately this returns an error

'invalid column name code'.

Here's what my actual SQL looks like:

SELECT 
    c.contactID, 
    CASE c.src 
     WHEN 0 THEN (SELECT tbl.code FROM CSA.dbo.Clients tbl WHERE clientID=c.uID)
     WHEN 1 THEN (SELECT tbl.scac FROM CSA.dbo.Carriers tbl WHERE carrierID=c.uID)
     WHEN 2 THEN (SELECT tbl.code FROM codeBase tbl WHERE cID=c.uID) 
    END as code,
    fName+' '+lname, 
    cType 

FROM contacts c 

WHERE fname LIKE '%mi%' OR code LIKE '%mi%'

So as you can see I'm doing the case to return the code based on what group and id they have. How do I search on that code column?

Upvotes: 0

Views: 63

Answers (3)

Steve Ford
Steve Ford

Reputation: 7753

Try:

SELECT 
    c.contactID, 
    CASE c.src 
     WHEN 0 THEN (SELECT tbl.code FROM CSA.dbo.Clients tbl WHERE clientID=c.uID)
     WHEN 1 THEN (SELECT tbl.scac FROM CSA.dbo.Carriers tbl WHERE carrierID=c.uID)
     WHEN 2 THEN (SELECT tbl.code FROM codeBase tbl WHERE cID=c.uID) 
    END as code,
    fName+' '+lname, 
    cType 

FROM contacts c 

WHERE fname LIKE '%mi%' OR 
    CASE c.src 
     WHEN 0 THEN (SELECT tbl.code FROM CSA.dbo.Clients tbl WHERE clientID=c.uID)
     WHEN 1 THEN (SELECT tbl.scac FROM CSA.dbo.Carriers tbl WHERE carrierID=c.uID)
     WHEN 2 THEN (SELECT tbl.code FROM codeBase tbl WHERE cID=c.uID) 
    END LIKE '%mi%'

Alternatively use a CTE:

;WITH CTE
AS
(
    SELECT 
        c.contactID, 
        CASE c.src 
         WHEN 0 THEN (SELECT tbl.code FROM CSA.dbo.Clients tbl WHERE clientID=c.uID)
         WHEN 1 THEN (SELECT tbl.scac FROM CSA.dbo.Carriers tbl WHERE carrierID=c.uID)
         WHEN 2 THEN (SELECT tbl.code FROM codeBase tbl WHERE cID=c.uID) 
        END as code,
        fName+' '+lname as fullname,
        fname, 
        cType 

    FROM contacts c 
)
SELECT *
FROM CTE
WHERE fname like '%mi%' or code like '%mi%'

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726559

There are at least three approaches to this:

  • Repeating the CASE inside WHERE - this is not recommended, because you repeat a lot of code between the SELECT and WHERE
  • Wrap the query into a SELECT *, and use its columns in the outer WHERE - this approach is reasonably portable
  • Use Common Table Expression - this approach is similar to the one above, but it leads to a somewhat cleaner query.

Upvotes: 1

Andy Nichols
Andy Nichols

Reputation: 3002

You can use a common table expression to get your result including the code column and then filter on it.

WITH CTE AS (
  SELECT 
    c.contactID, 
    CASE c.src 
     WHEN 0 THEN (SELECT tbl.code FROM CSA.dbo.Clients tbl WHERE clientID=c.uID)
     WHEN 1 THEN (SELECT tbl.scac FROM CSA.dbo.Carriers tbl WHERE carrierID=c.uID)
     WHEN 2 THEN (SELECT tbl.code FROM codeBase tbl WHERE cID=c.uID) 
    END as code,
    fName+' '+lname AS fullname, 
    fName,
    cType 

  FROM contacts c
)
SELECT contactID, code, fullname, cType FROM CTE
WHERE fname LIKE '%mi%' OR code LIKE '%mi%'

Upvotes: 1

Related Questions