Click here to Skip to main content
14,770,630 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
I have a question in sql server

Table 1 :Emp

id  |Flag | Sdate      | Edate      | dname
1   |2    | 2015-09-14 | 2015-09-25 |ceo
1   |3    | 2015-09-16 |2015-09-23  |hr
1   |1    | 2015-07-21 |2015-09-28  | ce
2   |3    | 2014-04-20 |2014-05-24  |Hr
3   |3    |2013-05-24  |2013-08-21  |hm
4   |2    |2015-07-28  |2015-07-30  |ho



Table 2 : emp1

id    |  Loc   | date
1     | hyd    |2015-09-19
1     | chen   |2015-05-24
1     | ben    |2015-07-21
2     | pune   |2015-04-23
3     |ce      |2013-05-26

here if emp1 table related date foll betwwn emp table sdate and edate then bring required colummns form emp1 tabel if not pass null values for required columns.
when we retrive loc column from 2nd table that time we must consider date between sdate and edate
if satisfy then we retrive loc column data other wise loc data column consider as null

based on above two tables I want output like below and

id    |  Flage    | dname   | loc
1     | 2         | ceo     | hyd
1     | 3         | hr      |hyd
1     | 1         | ce      | ben
2     | 3         | hr      |null
3     | 3         |hm       | ce
4     | 2         | ho      |null

I tried like below

select a.id, a.flag, a.dname, b.loc 
from emp a 
left join emp1 b on a.id = b.id 
and  b.date between a.sdate and a.edate 


this query not give expected result . please tell me how to wirte query to achive this task in sql server
Posted
Updated 6-Oct-15 23:46pm
v5

Try below query:
SELECT id, Flag, dname, 
(SELECT TOP 1 loc FROM Emp1 WHERE [date] BETWEEN tempTab.Sdate AND tempTab.Edate) AS loc 
FROM Emp AS tempTab
   
Comments
[no name] 7-Oct-15 2:20am
   
What is the result difference, can you describe here.
On the first look, you have to use INNER JOIN instead of LEFT JOIN.

Why? Please, read this: Visual Representation of SQL Joins[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900