i have two tables called

[Employee] which contains a username, recnum(pk)
[Consumer] which contains recnum(fk), Entrydate, points

one to many relationship... many entries in consumer for same recnum... i need to display the last date the consumer field has for each username...

the dashes are just added for spacing....

[Employee] ----------------------------- [Consumer]
recnum userid ------------------------- recnum ---- Entrydate - points
1-------- bob ----------------------------- 1 -----------1/4/2006 -- 23
2 ------- joe -------------------------------1 -----------1/5/2006 -- 34
3 ------- mary -----------------------------1 -----------2/6/2006 -- 53
4 ------- john ----------------------------- 2 -----------1/4/2006 -- 12
5 --------paul ------------------------------4 -----------1/6/2006 -- 8
-----------------------------------------------2-----------1/9/2006 -- 10

I need to return...
recnum - userid --- lastdate
1 --------- bob ----- 2/6/2006
2 --------- joe ----- 1/9/2006
4 --------- paul ----- 1/6/2006

using mssql , what would my query look like?


Thanks

Something like this:


SELECT *
FROM employee
INNER JOIN consumer
ON employee.recnum=consumer.recnum

Originally Posted by paaaaaaaaaa

Something like this:


SELECT *
FROM employee
INNER JOIN consumer
ON employee.recnum=consumer.recnum

select * from Employee left join consumer ON employee.recnum=consumer.recnum where employee.recnum=(select recnum from consumer where recnum=employee.recnum order by date desc limit 1);

Regards