February 4, 2015 at 9:40 pm
I need assistance with a query. It's part of a much larger query, however, if no data result is in table_1 based on two variables a and b, get the data from table_2 based on one variable a. Table_1 has multiple rows for variable a and Table_2 has a single row for variable a.
This works if I use a full outer join joining table_1 and table_2 on a as long as I put my where clause after each of the tables
but I want the flexibility to do it at the end since it is part of such a large query. Each time I try this, I end up with more rows than expected.
Generic Scenario:
select H from table_1
where a = X and b = Y
-- if no result
select I from table_2
where a = X
-- this seems to work
select isnull(H, I)
from
(select H from table_1
where a = X and b = Y) a
full outer join
(select I from table_2
where a = X) b
on a.a = b.a
-- this gives more rows than the single row from b that I'm expecting
select isnull(H, I)
from
(select H from table_1) a
full outer join
(select I from table_2) b
on a.a = b.a
where (a.a = X and a.b = Y) or (b.a = X)
-- this gives no rows when I am expecting a single row from b since a has no result
select isnull(H, I)
from
(select H from table_1) a
full outer join
(select I from table_2) b
on a.a = b.a
where (a.a = X and a.b = Y) and (b.a = X)
What am I missing? Any tips?
February 5, 2015 at 12:42 am
Looks like COALESCE is what you're looking for.
Would be easier to test with a little bit of sample data, though.
February 5, 2015 at 1:02 pm
You can put the SELECTs directly in the ISNULL, you don't have to use separate queries:
SELECT ISNULL((select H from table_1 where a = 'X' and b = 'Y'),
(select I from table_2 where a = 'X'))
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 5, 2015 at 2:59 pm
Thanks, I tried this and kept getting a syntax error with my live data but will try again knowing it is possible to do it this way.
February 6, 2015 at 2:09 am
Is there some reason you don't want to do this?
IF NOT EXISTS
(
select H
from table_1
where a = X and b = Y
)
-- if no result
select I
from table_2
where a = X
ELSE
select H
from table_1
where a = X and b = Y;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 10, 2015 at 9:07 am
dwain.c (2/6/2015)
Is there some reason you don't want to do this?
IF NOT EXISTS
(
select H
from table_1
where a = X and b = Y
)
-- if no result
select I
from table_2
where a = X
ELSE
select H
from table_1
where a = X and b = Y;
I always feel that doing a query that way is twice the work. When the first 'if not exists' is running against a large data set, you have to run that again when it has found data.
This is a tricky one, I expect that a coalesce function or the earlier posting of IsNull will return quicker.
Something like
SELECT COALESCE
(
(H from table_1 where a = X and b = Y),
(I from table_2 where a = X)
)
(have not tested this)
February 10, 2015 at 4:36 pm
erwin.oosterhoorn (2/10/2015)
(have not tested this)
Probably the best advice I can give you is that you should.
The result here is going to be highly dependent on the indexing available on the table.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 12, 2015 at 3:45 am
Don't worry, I will always test before I implement. This was more a 'gut feeling' and wanted to make sure that other users where not going to take this a 'tested advice'.
Interesting that you mention that this could differ depending on the indexing. Do you have experience using either of the methods?
Thanks
February 12, 2015 at 4:12 am
erwin.oosterhoorn (2/12/2015)
Don't worry, I will always test before I implement. This was more a 'gut feeling' and wanted to make sure that other users where not going to take this a 'tested advice'.Interesting that you mention that this could differ depending on the indexing. Do you have experience using either of the methods?
Thanks
-- runs both queries
SELECT COALESCE (
(SELECT TOP (1) H = 1 FROM sys.columns WHERE column_id = -1), -- no rows
(SELECT TOP (1) I = 2 FROM sys.columns where column_id = 1) -- rows
)
-- runs both queries
SELECT COALESCE (
(SELECT TOP (1) H = 1 FROM sys.columns WHERE column_id = 1), -- rows
(SELECT TOP (1) I = 2 FROM sys.columns where column_id = 1) -- rows
)
-- runs first query only
SELECT TOP (1) H = 1 FROM sys.columns WHERE column_id = 1
IF @@ROWCOUNT = 0 SELECT TOP (1) I = 2 FROM sys.columns where column_id = 1
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 12, 2015 at 4:18 am
Thanks for the quick explanation, took to read the full explanation of COALESCE and ISNULL to get a better understanding again.
Keep on learning...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply