CODEWITHSUNDEEP
sqlsql-server
SRJ
SRJ

Reputation: 198

Sql Condition Operator in Sql query

Please Correct my query to Select data. If there is an entry in LeaveApply Table having given date betwwn a from date and to date in the table, i should get the pK of table else zero.

please help

Select  A,
        B, 
ISNULL((select LeaveApply_Id from Tbl_Stud_Leave_Apply where Candidate_Id=120
and ('01/13/2014 12:00:00 AM' between Stud_LeaveFromDate and Stud_LeaveToDate)),0) As Leave from TableA where Condition.

Upvotes: 0

Views: 57

Answers (2)

StuartLC
StuartLC

Reputation: 107317

Note that if you do the lookup across a lot of results, you should consider a set-based approach to the lookup, e.g.

SELECT 
   c.*,
   ISNULL(l.LeaveApply_Id, 0) AS IsLeave
FROM 
  Candidates c
  LEFT OUTER JOIN Tbl_Stud_Leave_Apply l
    on l.Candidate_Id=c.ID AND @Date between Stud_LeaveFromDate and Stud_LeaveToDate;

Upvotes: 0

Naveen
Naveen

Reputation: 1502

You have to apply the isnull to the column, not to the whole query.

Try the below 

select ISNULL(LeaveApply_Id,0) AS ID from Tbl_Stud_Leave_Apply 
where Candidate_Id= @Candidate and (@date between Stud_LeaveFromDate and Stud_LeaveToDate)

In the above @Candidate and @date are dynamically passed to query.

Upvotes: 1

Related Questions