November 12, 2019 at 3:14 pm
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..
November 12, 2019 at 3:18 pm
Sounds like you want an INNER join to DepartmentDate.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 12, 2019 at 3:21 pm
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.
November 13, 2019 at 6:36 pm
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply