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.
Hmm
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.
thanx but..Originally Posted by Thantos
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;
When all else fails, read the instructions.
If you're posting code, use code tags: [code] /* insert code here */ [/code]
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.
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.
Thantos, you seem to be over complicating the matter? Either that, or I'm over simplfying or mis-understanding!Code:SELECT customers.account, Max(transactions.transactiondate) AS MaxOftransactiondate FROM customers LEFT JOIN transactions ON customers.account = transactions.account GROUP BY customers.account;
[edit]
Oh, on a third re-read, I guess I'm misunderstanding! Does the OP want just the date, or the full transaction record?
When all else fails, read the instructions.
If you're posting code, use code tags: [code] /* insert code here */ [/code]
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.
Hmm
