Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
webguru22
Starting Member
11 Posts |
 Posted - 2000-12-19 : 15:20:38
|
| Hey SQL gurus,Let's say I have 3 tables: tbl_firms, tbl_firm_employees, and tbl_Logins. Obviously employees are members of a firm. he tbl_logins has the date of the login and the ID(PK) of the employee, so we can see who logged in when. The code below will show the last time each employee logged in, his name, and what firm he works for. SELECT MAX(tbl_Logins.login_date) AS loginDate, tbl_firms.firm_name AS firmName, tbl_firms.ID AS firmID, tbl_firm_employees.employee_last_nameFROM tbl_firm_locations INNER JOIN tbl_firm_employees ON tbl_firm_locations.ID = tbl_firm_employees.parent_location INNER JOIN tbl_Logins ON tbl_firm_employees.ID = tbl_Logins.logon INNER JOIN tbl_firms ON tbl_firm_locations.parent_firm = tbl_firms.IDGROUP BY tbl_firms.firm_name, tbl_firms.ID, tbl_firm_employees.employee_last_nameNow what I NEED to do is pull a recordset containing the latest date ANYONE from each firm logged in, and who it was. There are only 2 different firms so the recordset should only contain 2 records. I wrote a query that just shows each firm and the DATE of the latest log-in, but when I join to the employee table to try to find out his name, I get a record for every employee, similar to the query above. Not what I need. Any ideas? To recap here is what my recordset would look like, if it worked correctly:[12/9/00] [Joe Smith] [ABC LAW][12/10/00] [Fran Tarkenton] [Rose Law Firm]Any help is appreciated! |
|
|
|
|
|
|
|
GROUP BY question