"Yaron Avior" <yavior@mercury.co.il> wrote in message
news:1b00d968.0312280717.d4e7e7e@posting.google.com...
> Hi,
> I have a question regarding join query syntax:
> In my DB there are Users table, and Transactions table (which has
> 'TransactionDate' and 'UserName' fields). I would like to display a
> list of all users (each user will appear only once), and for each user
> - his last transaction date.
> Please advice.
>
> Thanks,
> Yaron

"John Gilson" <jag@acm.org> wrote in message news:<dFCHb.58363$4F2.5274444@twister.nyc.rr.com>...
> "Yaron Avior" <yavior@mercury.co.il> wrote in message
> news:1b00d968.0312280717.d4e7e7e@posting.google.com...
> > Hi,
> > I have a question regarding join query syntax:
> > In my DB there are Users table, and Transactions table (which has
> > 'TransactionDate' and 'UserName' fields). I would like to display a
> > list of all users (each user will appear only once), and for each user
> > - his last transaction date.
> > Please advice.
> >
> > Thanks,
> > Yaron
>
> CREATE TABLE Users
> (
> user_name VARCHAR(25) NOT NULL PRIMARY KEY
> )
>
> CREATE TABLE UserTransactions
> (
> user_name VARCHAR(25) NOT NULL REFERENCES Users (user_name),
> transaction_date DATETIME NOT NULL
> CHECK (transaction_date <= CURRENT_TIMESTAMP),
> PRIMARY KEY (user_name, transaction_date)
> )
>
> SELECT user_name, MAX(transaction_date) AS latest_transaction_date
> FROM UserTransactions
> GROUP BY user_name
>
> If you want every user, regardless of whether he has a transaction entry,
> then do the following:
>
> SELECT U.user_name, MAX(T.transaction_date) AS latest_transaction_date
> FROM Users AS U
> LEFT OUTER JOIN
> UserTransactions AS T
> ON U.user_name = T.user_name
> GROUP BY U.user_name
>
> Regards,
> jag

> That worked great, thanks. though - when I wanted to add the
> Transaction name from Transaction table (join by TransactionID to
> UserTransactions table), I got multiplied recordset without the
> ability to get only one record per user. Any ideas?