Solved

Using date conditions in left outer joins

Posted on 2014-10-17
6
122 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
  • 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
(sql serv16)ssis 2016 question/check 1 71
where to find DTS instalation package for sql 2014 64 bit 3 11
TSQL XML Namespaces 7 23
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

860 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