>>Hi,
>>
>>UPDATED:
>>I have three tables. I want the SQL Select to include some records from one table, some records from the second table, but only one value from the third table, the most recent date
>>
>>Here is an example (simplified):
>>Table1
>>

>>user_name, user_pk 
>>

>>So, the above ecords from the Table1 should be in the query
>>Table2
>>

>>user_pk, user_email
>>

>>The column USER_EMAIL from the second table
>>Table3
>>

>>user_name, data_in
>>

>>Date_in - the last time this user logged in.
>>So the resulting query should look something like this
>>

>>USER_1     USER_1_EMAIL       USER1_LASTDATE_IN
>>USER_2     USER_2_EMAIL       USER2_LASTDATE_IN
>>

>>Here is what I tried
>>

>>select Table1.USER_NAME as USER_1, MAX(USER_EMAIL) as USER_1_EMAIL, MAX(DATE_IN) AS USER1_LASTDATE_IN FROM Table1
>>LEFT JOIN Table3 on Table1.USER_NAME = Table3.USER_NAME GROUP BY Table1.USER_NAME 
>>LEFT JOIN Table2 ON Table2.USER_PK = Table1.USER_PK
>>

>>The above works.
>>But I wonder if MAX(USER_EMAIL) will give the correct results. I was hoping that I could select USER_EMAIL from Table2 without MAX.
>>
>
>The way to figure out what you need is to do the query without GROUP BY and look at the results and then figure out whether grouping would give you what you'd need.
>
>As you show the table structures, I don't see why you'd need GROUP BY at all, but I suspect the issue is that you have multiple records for each user in Table3? If so, maybe a subquery to pull the most recent date out of Table3. Something like this:
>
>

>select Table1.USER_NAME as USER_1, USER_EMAIL as USER_1_EMAIL, LASTDATE AS USER1_LASTDATE_IN ;
>   FROM Table1 ;
>      LEFT JOIN (SELECT MAX(Date_IN) FROM Table3 WHERE Table3.USER_NAME = Table1.USER_NAME) ;
>      LEFT JOIN Table2 ON Table2.USER_PK = Table1.USER_PK
>

>
>If you're in SQL Server and the issue is that you need a different field from the one you're trying to maximize (for example, if you had a third column in Table3 that was the one you wanted in the result, but you wanted it from the record with the most recent date), then Christian's advice about using OVER is the way to go.
>
>Tamar

Sorry to trouble you again. One thing is not clear from the above SQL Select. How does the initial part of the SQL Select have LASTDATE? The subquery does not refer to the field MAX(DATE_IN) as LAST DATE?
Also, the Table1.USER_NAME shows as "could not be found" in the SSMS SQL Select.
What am I missing?

UPDATE:
I changed your SQL Select a little as this:

last_date = ( select top 1 date_in from table3 T3 where T3.user_name = T1.user_name order by T3.date_in desc ) from Table1 T1

The above SQL Select has no error and gives me the right results.