Very simple join query that is puzzling me

  • 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

  • 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]

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

  • 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'

  • 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

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

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