Solved

Using date conditions in left outer joins

Posted on 2014-10-17
6
133 Views
Last Modified: 2014-10-20
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
Comment
Question by:gvamsimba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40387641
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
 

Author Comment

by:gvamsimba
ID: 40387658
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
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40387692
It should but without the actual data to test it is little bit hard to tell. Can you post sample of data?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:gvamsimba
ID: 40387716
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
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 500 total points
ID: 40387800
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
 

Author Closing Comment

by:gvamsimba
ID: 40391718
good..
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question