Join With Min(date) 1

[pwilson]

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.

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

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.

 

[gmmastros]

I dont care which Joe entry shows up, just only one.

SELECT [!]E.Id, Name, DateItem, EmpId, Min(Note)[/!]
  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
[!]Group By E.Id, Name, DateItem, EmpId[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[pwilson]

Well say note is a text field.

ALTER TABLE TestDateLog ALTER COLUMN Note text NULL

The text, ntext, and image data types are invalid in this subquery or aggregate expression.

And what if there are more columns than just Note to be pulled from TestDateLog.

Thanks for looking at it.

 

[gmmastros]

Not fair! You changed the requirements on me.

In that case, I would wait for SQLDenis to reply while I go play a quick 9 holes of golf.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

quick fix, will pull only first 8000 characters

SELECT E.Id, Name, DateItem, EmpId, Min(convert(varchar(8000),Note))
  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
Group By E.Id, Name, DateItem, EmpId

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[pwilson]

Its an ugly hack but this works.

SELECT *
  FROM TestEmp E LEFT OUTER JOIN
      (SELECT DL.DateItem,DL.ID,DL.EmpId,DL.Note 
          FROM TestDateLog DL INNER JOIN 
              (SELECT [COLOR=#ff0000]MIN(DATEADD(ms,ID,DateItem))[/color] AS DateItem, EmpID
                  FROM TestDateLog
                  GROUP BY EmpID) S1 
          ON S1.DateItem=[COLOR=#ff0000]DATEADD(ms,ID,DL.DateItem))[/color] AS S2 
       ON S2.EmpId=E.ID;

Basically turn
Id date
1 10-1
2 10-1

to
10-1-2006 00:00:00.1
10-1-2006 00:00:00.2

So it
Pulls the .1 as the min and only finds that one.

 

[SQLDenis]

and another without convert

SELECT E.Id, Name, DateItem, EmpId, Note
  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,MIN(id) as MinID, EmpID
                  FROM TestDateLog
                  GROUP BY EmpID) S1 
          ON S1.DateItem=DL.DateItem
	AND S1.MinID = DL.ID)
AS S2 
       ON S2.EmpId=E.ID

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[pwilson]

The MinID makes sense. I dropped the Date=Date and it works the same.

thanks for the help.

 

[SQLDenis]

Thanks George I merely added 1 line to his query

tx 4 the star btw

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/