SQL join query

[Yaron Avior]

Hi,
I have a question regarding join query syntax:
In my DB there are Users table, and Transactions table (which has
'TransactionDat e' 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]

Re: SQL join query

"Yaron Avior" <yavior@mercury .co.il> wrote in message
news:1b00d968.0 312280717.d4e7e 7e@posting.goog le.com...[color=blue]
> Hi,
> I have a question regarding join query syntax:
> In my DB there are Users table, and Transactions table (which has
> 'TransactionDat e' 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[/color]

CREATE TABLE Users
(
user_name VARCHAR(25) NOT NULL PRIMARY KEY
)

CREATE TABLE UserTransaction s
(
user_name VARCHAR(25) NOT NULL REFERENCES Users (user_name),
transaction_dat e DATETIME NOT NULL
CHECK (transaction_da te <= CURRENT_TIMESTA MP),
PRIMARY KEY (user_name, transaction_dat e)
)

SELECT user_name, MAX(transaction _date) AS latest_transact ion_date
FROM UserTransaction s
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.transacti on_date) AS latest_transact ion_date
FROM Users AS U
LEFT OUTER JOIN
UserTransaction s AS T
ON U.user_name = T.user_name
GROUP BY U.user_name

Regards,
jag

[Yaron Avior]

Re: SQL join query

"John Gilson" <jag@acm.org> wrote in message news:<dFCHb.583 63$4F2.5274444@ twister.nyc.rr. com>...[color=blue]
> "Yaron Avior" <yavior@mercury .co.il> wrote in message
> news:1b00d968.0 312280717.d4e7e 7e@posting.goog le.com...[color=green]
> > Hi,
> > I have a question regarding join query syntax:
> > In my DB there are Users table, and Transactions table (which has
> > 'TransactionDat e' 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[/color]
>
> CREATE TABLE Users
> (
> user_name VARCHAR(25) NOT NULL PRIMARY KEY
> )
>
> CREATE TABLE UserTransaction s
> (
> user_name VARCHAR(25) NOT NULL REFERENCES Users (user_name),
> transaction_dat e DATETIME NOT NULL
> CHECK (transaction_da te <= CURRENT_TIMESTA MP),
> PRIMARY KEY (user_name, transaction_dat e)
> )
>
> SELECT user_name, MAX(transaction _date) AS latest_transact ion_date
> FROM UserTransaction s
> 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.transacti on_date) AS latest_transact ion_date
> FROM Users AS U
> LEFT OUTER JOIN
> UserTransaction s AS T
> ON U.user_name = T.user_name
> GROUP BY U.user_name
>
> Regards,
> jag[/color]

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

[Erland Sommarskog]

Re: SQL join query

Yaron Avior (yavior@mercury .co.il) writes:[color=blue]
> That worked great, thanks. though - when I wanted to add the
> Transaction name from Transaction table (join by TransactionID to
> UserTransaction s table), I got multiplied recordset without the
> ability to get only one record per user. Any ideas?[/color]

Something like:


SELECT ut.username, t.transactionna me, ut.transactiond ate
FROM (SELECT ut.username, transactiondate = MAX(ut.transact iondate)
FROM usertransaction s
GROUP BY ut.username) AS ut
JOIN usertransaction s ut2 ON ut.username = ut2.username
AND ut.transactiond ate = ut2.transaction date
JOIN transactions t ON ut.transactioni d = t.transactionid



--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[hasdy]

I also have the same requirement although with a minor difference.

I have a field of date and another field of time.

So how can we modify the query so that it will pick out the max date with the cooresponding max time? aka latest transaction?

Thanks,
hasdy