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