CODEWITHSUNDEEP
sqlsql-servert-sql
Filipp Krasovsky
Filipp Krasovsky

Reputation: 9

Case Statement for Joined Table

I have two tables:

Account ID | A | B
-------------------
1          | x | y
2          | c | f
3          |...|...

the first table is a general account list. The second table is a list of documents on hand for each acct:

Account ID | Doctype
---------------------
1          | chrgoff
2          | dtpmnt
2          | chrgoff
3          | lstpmt
3          | suit

For the report I'm creating, I need to create a column in the first table which stores the value of a flag, where 'Y' indicates that the second table contains the docType 'chrgoff' for a given account number.

I tried doing this with the following case statement, but the query won't execute at all:

'chgoff' = 
    CASE
      WHEN EXISTS(SELECT docType FROM table2 WHERE docType='chrgoff' and AccountID=table1.accountID) 
      THEN 'Y'
      ELSE 'N'
   END

I'm very new to T-SQL programming, so I would appreciate any help I could get! Let me know if I need to clarify anything. Thanks!

Upvotes: 0

Views: 49

Answers (2)

Shantanu Kher
Shantanu Kher

Reputation: 1054

Alternate way - You can flag a record by joining both the tables with left outer join. I believe it would be faster approach than EXIST with subquery.

SQL -

select t1.*, 
case when t2.account_id is not null then 'Y' else 'N' end as chgoff
from table1 t1 
left join table2 t2 on t1.account_id = t2.account_id and t2.doctype = 'chrgoff'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You code looks okay, but I would suggest:

(CASE WHEN EXISTS (SELECT docType FROM table2 t2 WHERE t2.docType = 'chrgoff' and t2.AccountID = table1.accountID) 
      THEN 'Y'
      ELSE 'N'
 END) as chgoff

The main differences are:

  • No single quotes on the column name. Only use single quotes for string and date constants.
  • Qualify the column references in the subquery. Don't depend on SQL's scoping rules. Be explicit.

As for as versus =. I prefer the former because it is standard SQL; = only assigns column aliases in SQL Server and related databases.

Upvotes: 2

Related Questions