Issue with left join

  • mcfarlandparkway

    SSCertifiable

    Points: 7621

    I have a select statement that

    selects studentid, studentjoiningdate, studentchargedhours, case when std.studentjoiningdate=.H.Date then std.studentchargedhours else 0 end

    from student std left join dbo.state st on st.stateid = std.state

    where std.studentid = '3456' --- hece it returns here 5 rows for this id.

    left join DepartmentDate H on std.State = H.State  -- here it returning all the rows.

    there are no rows matching in the DepartmentDate with student table joining date columns. how to avoid these rows to pick only 5 rows

     

    I have to do left join with departmentdate table , there is case statement in select list i am comparing std table joining date with departmentdate table date. if it matches then do some logic..

     

     

  • Phil Parkin

    SSC Guru

    Points: 243845

    Sounds like you want an INNER join to DepartmentDate.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • DesNorton

    SSC-Insane

    Points: 22848

    Your SQL is not valid.

    Your description of the problem does not make sense.

     

    Kindly provide readily consumable sample data, your expected outcome, and a functioning SQL statement.

  • jonathan.crawford

    SSCertifiable

    Points: 6366

    Also, you don't have to have a LEFT JOIN just to make a CASE statement work. The JOIN operation is about matching, your CASE statement is simply saying when to return something and what to return.

    That said, although the utility of CASE isn't dependent on the LEFT JOIN or not, if you have tried to join on std.studentjoiningdate=H.date and get no results, then your CASE statement is never going to return anything but 0 either.

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

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

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