FIND - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

FIND

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-04-16 : 06:13:58

Hi

So I have two simple tables (as below).

Sales table:

sale_date sale_amount

---------- -----------

2009-01-01 120.50

2009-01-02 115.00

2009-01-03 140.80

2009-01-04 100.50

Calendar table:

calendar_date holiday_name

------------- ----------------

2009-01-01 New Year's Day

2009-01-02 NULL

2009-01-03 NULL

2009-01-04 NULL

2009-01-05 NULL

To retrieve sales data for holiday dates, I write the query as

SELECT sale_date, sale_amount

FROM Sales AS S

WHERE sale_date IN (SELECT sale_date

FROM Calendar AS C

WHERE holiday_name IS NOT NULL)

Can you guess what will be the outcome of the above query and WHY(this is the important part!!)???

Sachin.Nand

2937 Posts

Posted - 2010-04-16 : 06:33:16

Outcome:- Will cause a execution time error.
WHY :- Because there is no sale_date column in calendar table.

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-16 : 07:12:36

Assuming you would like to use calendar_date in your subquery and the date are always stored without time part.
Out put would be

2009-01-01 120.50

Why?
Your subselect returns '2009-01-01' because that is the only record with holiday_name IS NOT NULL.

So your query looks like this

SELECT sale_date, sale_amount
FROM Sales AS S
WHERE sale_date IN (2009-01-01)

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources