Very simple join query that is puzzling me

Question

Very simple join query that is puzzling me

michielbijnen

Ten Centuries

Points: 1308
More actions
April 4, 2014 at 9:00 am

#306310

Hi,
Perhaps a misinterpretation but I am stuck in a very simple query that's puzzling me ...
Situation:
I have 2 tables (I'll stick to the relevant fields only)
1. PERDAT with fields (1) "year" (int), (2) "period" (varchar(3)), (3) "startdate" (datetime) and (4) "enddate" (datetime)
2. PROJECTS with fields (1) "projectnumber" (varchar(20), (2) "startdate-contract" (datetime) and (3) "enddate_contract" (datetime)
For every project I would like to have all periods from PERDAT that belong to the year of the startdate of the project. Only the record in the perdat-table that has the startdate of the project between the start and enddate in the perdat-table should show more info.
My query is:
SELECTpd.year,
pd.period,
pd.startdate,
df.projectnumber,
df.startdate_contract,
df.enddate_contract,
MONTH(df.startdate_contract) AS month
FROMperdat pd (NOLOCK)
LEFT OUTER JOIN ProjectDateFields df (NOLOCK) ON df.startdate_contract BETWEEN pd.startdate AND pd.enddate
WHEREdf.projectnumber = 'CR1003753'
The resultset I get:
yearperiodstartdateprojectnumberstartdate_contractenddate_contractmonth
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
What I would like to have is:
yearperiodstartdateprojectnumberstartdate_contractenddate_contractmonth
201412014-01-01 00:00:00.000NULLNULLNULLNULL
201422014-02-01 00:00:00.000NULLNULLNULLNULL
201432014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
201442014-04-01 00:00:00.000NULLNULLNULLNULL
201452014-05-01 00:00:00.000NULLNULLNULLNULL
201462014-06-01 00:00:00.000NULLNULLNULLNULL
201472014-07-01 00:00:00.000NULLNULLNULLNULL
201482014-08-01 00:00:00.000NULLNULLNULLNULL
201492014-09-01 00:00:00.000NULLNULLNULLNULL
2014102014-10-01 00:00:00.000NULLNULLNULLNULL
2014112014-11-01 00:00:00.000NULLNULLNULLNULL
2014122014-12-01 00:00:00.000NULLNULLNULLNULL
I thought it was a simple left join ...
What do I overlook?
Thanks!
regards
Michiel

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 1

Please read the article in my signature about posting questions. We will need some sample tables and sample data to test our solutions. Thanks

After reading the problem the issue is with your where statement. You have now made this an inner join. remove the where statement and replace it with an AND as part of your join criteria.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Sarah Wagner Hall of Fame Points: 3178 More actions · Answer 2

As soon as you added df to where clause it became an inner join.

Sarah Wagner Hall of Fame Points: 3178 More actions · Answer 3

Try this...

SELECTpd.year,

pd.period,

pd.startdate,

df.projectnumber,

df.startdate_contract,

df.enddate_contract,

MONTH(df.startdate_contract) AS month

FROMperdat pd (NOLOCK)

LEFT OUTER JOIN ProjectDateFields df (NOLOCK) ON df.startdate_contract BETWEEN pd.startdate AND pd.enddate

and df.projectnumber = 'CR1003753'

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

Sarah Wagner (4/4/2014)
Try this...
SELECTpd.year,
pd.period,
pd.startdate,
df.projectnumber,
df.startdate_contract,
df.enddate_contract,
MONTH(df.startdate_contract) AS month
FROMperdat pd (NOLOCK)
LEFT OUTER JOIN ProjectDateFields df (NOLOCK) ON df.startdate_contract BETWEEN pd.startdate AND pd.enddate
and df.projectnumber = 'CR1003753'

Or try it without the NOLOCK hints which will only make your queries return inconsistent information (you can have either missing rows or duplicate rows).

Check the following articles on this hint.

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2