PDA

View Full Version : For the SQL experts out there



Boomba
11-16-2005, 04:40 PM
I have a SQL question..didnt know wher else to ask. I have two tables: 1) customer table & 2) transactions. They both have an account column and account is a primary key in the customer table of course.

I want the SQL instructions to get the names of the customers' latest transaction (decided by date column) for each customer.

thanx in advance
Boomba

Thantos
11-16-2005, 04:50 PM
Two ways:
LEFT JOIN table2 ON table2.account=table1.account

Thats good if its possible that table1.account doesn't exist and you still want the data from table2.

Other way
SELECT blah blah blah
FROM table1, table2
WHERE table1.account=table2.account

B0bDole
11-16-2005, 04:56 PM
related to sql I've been asking everyone for their suggestions on a good really basic intro to SQL tutorial.

Thantos
11-16-2005, 04:57 PM
I did the sink or swim method with a project :)

SMurf
11-16-2005, 05:24 PM
Awwwwwwww yeah, Thantos. Countless hours spent writing the same thing over and over again with minor changes. My colleagues kept glaring at me every time I tried to tidy it all away into a function or two. :o

Boomba
11-16-2005, 05:32 PM
Two ways:
LEFT JOIN table2 ON table2.account=table1.account

Thats good if its possible that table1.account doesn't exist and you still want the data from table2.

Other way
SELECT blah blah blah
FROM table1, table2
WHERE table1.account=table2.account

thanx but..
without trying that i know that would only return all the transactions that have the same account.....not what i'm looking for...what i meant is that i want to get EACH customers's LATEST transaction. ...so there should only be one customer (name and transaction balance) returned for every Account.

Hammer
11-16-2005, 06:47 PM
One way:


SELECT customers.account, Max(transactions.transactiondate) AS MaxOftransactiondate
FROM customers INNER JOIN transactions ON customers.account = transactions.account
GROUP BY customers.account;

Thantos
11-16-2005, 10:26 PM
heh sorry guess I didn't fully read the post.

If you want the full transaction and customer information its gonna be difficult.
You could use a subquery to retrieve the transaction information. However I personally would go a different route. I would retrieve the customer data in one query. And then in another query I would get the transaction data.
If you are willing to do a little restructuring you could make this a lot easier. Just add another column to your customer data that holds the transaction id for the last transaction.

nickname_changed
11-16-2005, 11:07 PM
related to sql I've been asking everyone for their suggestions on a good really basic intro to SQL tutorial.
Theres lots of versions of "SQL" out there. You've got Microsoft SQL Server, Oracle and MySQL. Although they call claim to use a "SQL" query language, none of them are really compatible (except the most basic queries).

If you want to start with Microsoft SQL Server, SQLTeam.com is a good place to start.

Hammer
11-17-2005, 02:36 AM
In addition to my last post, the following will produce a list of all customers with last transaction date, including customers that don't have any transactions. In the latter case, the Max trans date field will be NULL.



SELECT customers.account, Max(transactions.transactiondate) AS MaxOftransactiondate
FROM customers LEFT JOIN transactions ON customers.account = transactions.account
GROUP BY customers.account;

Thantos, you seem to be over complicating the matter? Either that, or I'm over simplfying or mis-understanding! ;)

[edit]
Oh, on a third re-read, I guess I'm misunderstanding! Does the OP want just the date, or the full transaction record?

Thantos
11-17-2005, 08:21 AM
I'm assuming full transaction record.

Jaqui
11-17-2005, 05:04 PM
Bob,
Here (http://www.1keydata.com/sql/sql.html) is a foundation SQL tutorial and command / function reference.

B0bDole
11-18-2005, 12:25 AM
06:04 PM
Bob,
Here is a foundation SQL tutorial and command / function reference.

only 2 hrs after my test. Oh well, we'll see how well I guessed on that one.