Using date conditions in left outer joins

Hi below is a sample of my script which is also bringing null values.  The reason why I added that null condition in OR is because
There are some records in output table where periodid is null and I want my result to bring all records which full fill my Date condition along with those nulls but only those nulls which fall within my date range.

But the below query is also bringing those Nulls which are outside that date range.

Any suggestions to get my desired results please ?

Many Thanks


Select * from output c
Left outer join period p
On p. periodid = c. Periodid
Where ( p.periodstartdate > '20140401' OR
c.periodid IS NULL)
gvamsimbaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Haris DulicIT ArchitectCommented:
Because the query will return records that satisfy any of the conditions you set up so if the c.periodid is null it will be included no mater what is the p.periodstartdate..

Can you try this :

Select * from output c
right outer join period p
On p. periodid = c. Periodid
Where  p.periodstartdate > '20140401' 

Open in new window

gvamsimbaAuthor Commented:
Hi Samo , but I am assuming right outer joins will still return NULLS in case of no match. And by this I will still loose out my null records which satisfy my date condition.
Am I right ?
Haris DulicIT ArchitectCommented:
It should but without the actual data to test it is little bit hard to tell. Can you post sample of data?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

gvamsimbaAuthor Commented:
Output table
Outputid  periodid
1              1234
2              4567
3.              null
4.              Null



Period table
Periodid  periodstartdate
1234          01/05/ 2014
4567          05/06/ 2014
7896          01/07/ 2014
9899          01/02/2013

So in my result set I am expecting output for only periodis 1243 , 4567 and 7896 as they satisfy my date condition.
But my current query is also bringing periodid 9899 but in fact it shouldn't as it falls outside my date range
Haris DulicIT ArchitectCommented:
So far i got to this  option:

Select c.outputid, c.periodid, p.periodstartdate from output c
Left outer join period p
On p. periodid = c. Periodid
Where p.periodstartdate > '20140104' 
union all
select 0 as id, p.periodid, p.periodstartdate
from period p 
Where p.periodstartdate > '20140104' 
and p.periodid not in (select distinct periodid as periodid from output where periodid is not null)

Open in new window


you can test it here :

http://sqlfiddle.com/#!3/e48e1/25

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gvamsimbaAuthor Commented:
good..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.