• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 146
  • Last Modified:

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)
0
gvamsimba
Asked:
gvamsimba
  • 3
  • 3
1 Solution
 
Haris DjulicCommented:
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

0
 
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 ?
0
 
Haris DjulicCommented:
It should but without the actual data to test it is little bit hard to tell. Can you post sample of data?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
0
 
Haris DjulicCommented:
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
0
 
gvamsimbaAuthor Commented:
good..
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now