For the SQL experts out there

This is a discussion on For the SQL experts out there within the A Brief History of Cprogramming.com forums, part of the Community Boards category; I have a SQL question..didnt know wher else to ask. I have two tables: 1) customer table & 2) transactions. ...

  1. #1
    Registered User Boomba's Avatar
    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

  2. #2
    & the hat of GPL slaying Thantos's Avatar
    Join Date
    Sep 2001
    Posts
    5,681
    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

  3. #3
    Bob Dole for '08 B0bDole's Avatar
    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

  4. #4
    & the hat of GPL slaying Thantos's Avatar
    Join Date
    Sep 2001
    Posts
    5,681
    I did the sink or swim method with a project

  5. #5
    Registered /usr
    Join Date
    Aug 2001
    Location
    Newport, South Wales, UK
    Posts
    1,263
    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.

  6. #6
    Registered User Boomba's Avatar
    Join Date
    Jun 2003
    Posts
    89
    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.

  7. #7
    End Of Line Hammer's Avatar
    Join Date
    Apr 2002
    Posts
    6,231
    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]

  8. #8
    & the hat of GPL slaying Thantos's Avatar
    Join Date
    Sep 2001
    Posts
    5,681
    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.

  9. #9
    Banned nickname_changed's Avatar
    Join Date
    Feb 2003
    Location
    Australia
    Posts
    986
    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.

  10. #10
    End Of Line Hammer's Avatar
    Join Date
    Apr 2002
    Posts
    6,231
    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 Attached Images  
    When all else fails, read the instructions.
    If you're posting code, use code tags: [code] /* insert code here */ [/code]

  11. #11
    & the hat of GPL slaying Thantos's Avatar
    Join Date
    Sep 2001
    Posts
    5,681
    I'm assuming full transaction record.

  12. #12
    Registered User Jaqui's Avatar
    Join Date
    Feb 2005
    Posts
    416
    Bob,
    Here is a foundation SQL tutorial and command / function reference.
    Quote Originally Posted by Jeff Henager
    If the average user can put a CD in and boot the system and follow the prompts, he can install and use Linux. If he can't do that simple task, he doesn't need to be around technology.

  13. #13
    Bob Dole for '08 B0bDole's Avatar
    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

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Speed/size advantage to SQL?
    By MK27 in forum Tech Board
    Replies: 15
    Last Post: 06-26-2009, 11:28 AM
  2. OOP Question DB Access Wrapper Classes
    By digioz in forum C# Programming
    Replies: 2
    Last Post: 09-07-2008, 04:30 PM
  3. Embedded SQL
    By sarac in forum C Programming
    Replies: 1
    Last Post: 05-04-2006, 09:09 AM
  4. Replies: 1
    Last Post: 03-21-2006, 06:52 AM
  5. Problem with embedded SQL in C/C++ (ECPG)
    By NeuralClone in forum C Programming
    Replies: 4
    Last Post: 10-21-2005, 05:16 PM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21