Code:
Employees
Id Name
1 Joe
2 Carol
3 Susan
DateLog
ID date Employee Note
1 10-1 1 Fun
2 10-1 1 Great
3 10-10 3 monkeys rule
3 12-12 3 i like elephants
So I want All employees with their earliest date applied, but also the note from the item. Since Susan has two dates that are the same, I end up getting results with two entries for her .
I cant use the DateLog.ID because The times entered do not correlate with increasing ID values.
Code:
Testing code
CREATE TABLE TestEmp(id int identity(1,1) primary key, Name varchar(10))
CREATE TABLE TestDateLog(
id int identity(1,1) primary key,
DateItem datetime NULL,
EmpID int NULL,
Note varchar(30) NULL
)
INSERT INTO TestEmp(Name) VALUES('Joe')
INSERT INTO TestEmp(Name) VALUES('Carol')
INSERT INTO TestEmp(Name) VALUES('Susan')
INSERT INTO TestDateLog(DateItem,EmpID,Note)VALUES('10/1/2006',1,'Fun')
INSERT INTO TestDateLog(DateItem,EmpID,Note)VALUES('10/1/2006',1,'Great')
INSERT INTO TestDateLog(DateItem,EmpID,Note)VALUES('10/10/2006',3,'monkeys rule')
INSERT INTO TestDateLog(DateItem,EmpID,Note)VALUES('12/12/2006',3,'i like elephants')
SELECT *
FROM TestEmp E LEFT OUTER JOIN
(SELECT DL.DateItem,DL.ID,DL.EmpId,DL.Note
FROM TestDateLog DL INNER JOIN
(SELECT MIN(DateItem) AS DateItem, EmpID
FROM TestDateLog
GROUP BY EmpID) S1
ON S1.DateItem=DL.DateItem) AS S2
ON S2.EmpId=E.ID;
Results
Code:
1 Joe 2006-10-01 00:00:00.0 1 1 Fun
1 Joe 2006-10-01 00:00:00.0 2 1 Great
2 Carol (null) (null) (null) (null)
3 Susan 2006-10-10 00:00:00.0 3 3 monkeys rule
I dont care which Joe entry shows up, just only one.
Thanks for any help. And Im hoping to not do this using a WHERE IN, if possible.