Select tbl1.a if exists otherwise tbl2.a

Question

Select tbl1.a if exists otherwise tbl2.a

stephenmbell

SSC Eights!

Points: 926
More actions
May 6, 2010 at 12:04 pm

#222230

Not even sure if my subject does what I am trying to accomplish justice but here goes...
I have a table - DailySales. It has fields for Date, Location, Sales.
I have another table - OverrideDailySales. Same fields, Date, Location, Sales.
It is done this way because a) DailySales is imported from another system via an automated process and b) we OverrideDailySales to allow for trending and to replace bad data in individual circumstances.
I have a Stored Procedure - GetSales - I currently pass it a date range and a location, and I get the sales (from daily sales) for this location for the date range.
What I would like to do is select the sales information from the Override table if it exists for that date / location, otherwise select the information from daily sales.
For some reason, I always struggle thinking SQL with this type of stuff - I am not sure if I should be using IF EXISTS or CASE WHEN...
Any help is greatly appreciated.
Thanks
sb

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

-- COALESCE returns the first non-null value from the list provided; it is a param array, so an large number of arguments can be passed to check for non-nulls.

SELECT COALESCE(tbl1.a,tbl2.a,'no value')

FROM tbl1

Left outer join tbl2 ON tbl1.JOINCRITERIA = tbl2.JOINCRITERIA

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Lowell (5/6/2010)
-- COALESCE returns the first non-null value from the list provided; it is a param array, so an large number of arguments can be passed to check for non-nulls. SELECT COALESCE(tbl1.a,tbl2.a,'no value') FROM tbl1 Left outer join tbl2 ON tbl1.JOINCRITERIA = tbl2.JOINCRITERIA

Me thinks you want to swap tbl1.a, tbl2.a in the coalesce statement.

-- COALESCE returns the first non-null value from the list provided; it is a param array, so an large number of arguments can be passed to check for non-nulls.

SELECT COALESCE(tbl2.a,tbl1.a,'no value')

FROM

tbl1 -- main table

Left outer join tbl2 -- override table (if override data exists)

ON tbl1.JOINCRITERIA = tbl2.JOINCRITERIA

Lowell SSC Guru Points: 323518 More actions · Answer 3

i was a little unsure on which to select; his thread title said "Select tbl1.a if exists otherwise tbl2.a",

and i glossed over the implication that whether tbl1 or tbl2 = override;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

True, but looking at the Sales and SalesOverride tables, just made sense to qualify it so that it was clearer.

I use a similar technique for permissions overrides in a process I have running here for a couple of our MSP systems where we need to assign permissions programmatically.