For the SQL experts out there

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

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

related to sql I've been asking everyone for their suggestions on a good really basic intro to SQL tutorial.

I did the sink or swim method with a project :)

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

Quote:

---

Originally Posted by Thantos

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.

One way:

Code:

---

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

---

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.

Quote:

---

Originally Posted by B0bDole

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.

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.

Code:

---

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?

I'm assuming full transaction record.

Bob,
Here is a foundation SQL tutorial and command / function reference.

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.