Solved

Using date conditions in left outer joins

Posted on 2014-10-17
6
129 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
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 

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

Independent Software Vendors: 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!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

729 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