February 13, 2013 at 5:48 am
I have a query that returns, amongst other things, an Id. Lets call this recordset abc.
I also have a table (xyz) with a list of zero to many Ids that can be joined to the Id in the above recordset abc.
The question is:
By using only one statement (it's simple with 2) and without using dynamic SQL, how would I return the following ...
If table xyz is empty, then give me all the records in abc.
If table xyz has 1 or more Ids in it, return only records in abc that have the Ids from xyz.
So it's something along the lines of:
SELECT abc.*
FROM abc
LEFT JOIN xyz on abc.Id = xyz.Id
WHERE xyz.Id is CASE WHEN IF NOT EXISTS (SELECT * FROM xyz) THEN null ELSE not null END
... but obviously this syntax doesn't work.
Any ideas?
February 13, 2013 at 6:42 am
One way:
SELECT abc.*
FROM dbo.abc
JOIN dbo.xyz ON dbo.abc.id = dbo.xyz.id
WHERE EXISTS ( SELECT TOP 1
*
FROM dbo.xyz )
UNION ALL
SELECT *
FROM dbo.abc
WHERE NOT EXISTS ( SELECT TOP 1
*
FROM dbo.xyz );
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 13, 2013 at 6:56 am
Another way:
SELECT abc.*
FROM dbo.abc abc
WHERE NOT EXISTS (SELECT 1 FROM dbo.xyz)
OR abc.id IN (SELECT ID FROM dbo.xyz)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 13, 2013 at 8:23 am
Arrg ... so simple! Thanks - that's perfect.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply