T-SQL query to check number of existences

Posted by abatishchev on Stack Overflow See other posts from Stack Overflow or by abatishchev
Published on 2010-02-13T19:28:04Z Indexed on 2010/05/17 2:40 UTC
Read the original article Hit count: 382

I have next approximate tables structure:

accounts:
ID INT,
owner_id INT,
currency_id TINYINT

related to

clients:
ID INT

and

currency_types:
ID TINYINT,
name NVARCHAR(25)

I need to write a stored procedure to check existence of accounts with specific currency and all others, i.e. client can have accounts in specific currency, some other currencies and both.

I have already written this query:

SELECT
    ISNULL((
    SELECT 1
    WHERE EXISTS
    (
        SELECT 1
        FROM [accounts] AS A, [currency_types] AS CT
        WHERE
            A.[owner_id] = @client -- sp param
        AND A.[currency_id] = CT.[ID]
        AND CT.[name] = N'Ruble'
    )), 0) AS [ruble],
    ISNULL((
    SELECT 1
    WHERE EXISTS
    (    
        SELECT A.[ID]
        FROM [accounts] AS A, [currency_types] AS CT
        WHERE
            A.[owner_id] = @client 
        AND A.[currency_id] = CT.[ID]
        AND CT.[name] != N'Ruble'
    )), 0) AS [foreign]

Is it possible to optimize it? I'm new to (T)SQL, so thanks in advance!

© Stack Overflow or respective owner

Related posts about sql

Related posts about tsql