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.

Boomba · 11-16-2005, 05:32 PM

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.

**Hammer** · 11-16-2005, 06:47 PM

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;

**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

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.

**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.

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?

**Thantos** · 11-17-2005, 08:21 AM

I'm assuming full transaction record.

Jaqui · 11-17-2005, 05:04 PM

Bob,
Here 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.

11-16-2005, 04:40 PM	#1
Boomba Registered User Join Date: Jun 2003 Posts: 89	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
Boomba is offline

11-16-2005, 04:50 PM	#2
Thantos & the hat of GPL slaying Join Date: Sep 2001 Posts: 5,732	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
Thantos is offline

11-16-2005, 04:56 PM	#3
B0bDole Bob Dole for '08 Join Date: Sep 2004 Posts: 618	related to sql I've been asking everyone for their suggestions on a good really basic intro to SQL tutorial. __________________ Hmm
B0bDole is offline

11-16-2005, 04:57 PM	#4
Thantos & the hat of GPL slaying Join Date: Sep 2001 Posts: 5,732	I did the sink or swim method with a project
Thantos is offline

11-16-2005, 05:24 PM	#5
SMurf Registered User Join Date: Aug 2001 Location: Newport, South Wales, UK Posts: 1,094	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.
SMurf is offline

11-16-2005, 06:47 PM	#7
Hammer End Of Line Join Date: Apr 2002 Posts: 6,240	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]
Hammer is offline

11-16-2005, 10:26 PM	#8
Thantos & the hat of GPL slaying Join Date: Sep 2001 Posts: 5,732	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.
Thantos is offline

11-17-2005, 02:36 AM	#10
Hammer End Of Line Join Date: Apr 2002 Posts: 6,240	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? Attached Images __________________ When all else fails, read the instructions. If you're posting code, use code tags: [code] /* insert code here */ [/code]
Hammer is offline

11-17-2005, 08:21 AM	#11
Thantos & the hat of GPL slaying Join Date: Sep 2001 Posts: 5,732	I'm assuming full transaction record.
Thantos is offline

11-18-2005, 12:25 AM	#13
B0bDole Bob Dole for '08 Join Date: Sep 2004 Posts: 618	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
B0bDole is offline

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Speed/size advantage to SQL?	MK27	Tech Board	15	06-26-2009 11:28 AM
OOP Question DB Access Wrapper Classes	digioz	C# Programming	2	09-07-2008 04:30 PM
Embedded SQL	sarac	C Programming	1	05-04-2006 09:09 AM
functions seems not to exit from a certain code block	jaro	C Programming	1	03-21-2006 07:52 AM
Problem with embedded SQL in C/C++ (ECPG)	NeuralClone	C Programming	4	10-21-2005 05:16 PM