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
'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]

"Yaron Avior" <ya****@mercury.co.il> wrote in message
news:1b*************************@posting.google.co m...

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

[Yaron Avior]

"John Gilson" <ja*@acm.org> wrote in message news:<dF*********************@twister.nyc.rr.com>. ..

"Yaron Avior" <ya****@mercury.co.il> wrote in message
news:1b*************************@posting.google.co m...

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?

[Erland Sommarskog]

Yaron Avior (ya****@mercury.co.il) writes:

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?

Something like:
SELECT ut.username, t.transactionname, ut.transactiondate
FROM (SELECT ut.username, transactiondate = MAX(ut.transactiondate)
FROM usertransactions
GROUP BY ut.username) AS ut
JOIN usertransactions ut2 ON ut.username = ut2.username
AND ut.transactiondate = ut2.transactiondate
JOIN transactions t ON ut.transactionid = t.transactionid

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...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