MySQL problem with multiple-tables

Question

newb question there. Having MySQL database/tables structure below:

CREATE DATABASE `dbTest`

CREATE  TABLE IF NOT EXISTS `dbTest`.`tbUser` (

  `UserId` INT UNSIGNED NOT NULL AUTO_INCREMENT ,

  `Username` VARCHAR(45) NOT NULL ,

  PRIMARY KEY (`UserId`) )

ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `dbTest`.`tbTransactionType` (

  `TypeId` INT UNSIGNED NOT NULL AUTO_INCREMENT ,

  `Name` VARCHAR(45) NOT NULL ,

  PRIMARY KEY (`TypeId`) )

ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `dbTest`.`tbTransaction` (

  `TransactionId` INT NOT NULL AUTO_INCREMENT ,

  `UserId` INT UNSIGNED NOT NULL ,

  `TransactionType` INT UNSIGNED NOT NULL ,

  `Balance` DOUBLE NOT NULL ,

  `Date` DATETIME NOT NULL ,

  PRIMARY KEY (`TransactionId`) ,

  INDEX `FK_tbTransaction_tbUser_UserId` (`UserId` ASC) ,

  INDEX `FK_tbTransaction_tbTransactionType_TransactionId` (`TransactionType` ASC) ,

  CONSTRAINT `FK_tbTransaction_tbUser_UserId`

    FOREIGN KEY (`UserId` )

    REFERENCES `dbTest`.`tbUser` (`UserId` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `FK_tbTransaction_tbTransactionType_TransactionId`

    FOREIGN KEY (`TransactionType` )

    REFERENCES `dbTest`.`tbTransactionType` (`TypeId` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB

INSERT INTO `dbTest`.`tbUser` (`UserId`, `Username`) VALUES ('1', 'User1');
INSERT INTO `dbTest`.`tbTransactionType` (`TypeId`, `Name`) VALUES ('1', 'Deposite');
INSERT INTO `dbTest`.`tbTransactionType` (`TypeId`, `Name`) VALUES ('2', 'Withdraw');

INSERT INTO `dbTest`.`tbTransaction` (`TransactionId`, `UserId`, `TransactionType`, `Balance`, `Date`) VALUES (NULL, '1', '1', '200', NOW())
INSERT INTO `dbTest`.`tbTransaction` (`TransactionId`, `UserId`, `TransactionType`, `Balance`, `Date`) VALUES (NULL, '1', '2', '100', NOW())
INSERT INTO `dbTest`.`tbTransaction` (`TransactionId`, `UserId`, `TransactionType`, `Balance`, `Date`) VALUES (NULL, '1', '1', '20', NOW())
INSERT INTO `dbTest`.`tbTransaction` (`TransactionId`, `UserId`, `TransactionType`, `Balance`, `Date`) VALUES (NULL, '1', '2', '10', NOW())

I would like to get the last entry of each type of transaction for the UserId 1

This is my SQL query:

SELECT U.Username, TT.Name, T.Balance, T.Date
FROM tbUser U
INNER JOIN tbTransaction T ON T.UserId = U.UserId
INNER JOIN tbTransactionType TT ON TT.TypeId = T.TransactionType
WHERE U.UserId = 1

And the result is:

Username    Name        Balance Date
User1   Deposite    200     2010-11-26 23:11:40
User1   Deposite    20      2010-11-26 23:14:56
User1   Withdraw    100     2010-11-26 23:11:58
User1   Withdraw    10      2010-11-26 23:14:56

When I would like to get something like:

Username    Name        Balance Date
User1       Deposite    20      2010-11-26 23:14:56
User1       Withdraw    10      2010-11-26 23:14:56

I'm sure the solution isn't that hard but I can't figure out right now...

I've also tried a GROUP BY TT.TypeId with no success.

Thanks!

jon_darkstar · Accepted Answer · 2010-11-27 05:06:19Z

2

I think this should do it, and it won't be an issue if you add more transaction types. However, I haven't quite tested bc I don't exactly have an "isomorphic" (probably not strictly the correct term) set of tables handy to test on. Please let me know even if you're solved, I'm curious.

SELECT U.Username, TT.Name, T.Balance, T.Date
FROM tbUser U
INNER JOIN tbTransaction T ON T.UserId = U.UserId
INNER JOIN tbTransactionType TT ON TT.TypeId = T.TransactionType
WHERE U.UserId = 1 AND 
       T2.Date = (SELECT MAX(T2.date) 
                 FROM tbTransaction T2 WHERE 
                 T2.UserID = U.UserId, T2.TransactionType = T.TransactionType)

edited Nov 27, 2010 at 5:06

answered Nov 27, 2010 at 4:34

jon_darkstar

16.9k7 gold badges35 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Cybrix Over a year ago

Hi, thanks for your answer. I tried that one too. But it wont work if the entries in tbTransaction aren't in the good order. I mean, two deposite then a withdraw are made. Also, what happens if more TransactionType are added? That Limit 2 won't work.

jon_darkstar Over a year ago

so you do want the last two of each type? thats not whats your sample answer reflects - it is either the last two total, or the last one of each type

Cybrix Over a year ago

Oops! sorry. The last entry of each Transaction Type. I edited the question.

jon_darkstar Over a year ago

@Cybrix - I see you've found a good answer, glad you got it. Still, please take a look - I'm curios about this.

Cybrix Over a year ago

This works too! I am now unsure which one is better (speed). Yours is easier to understand though.

|

Adriaan Stander · Accepted Answer · 2010-11-27 04:46:00Z

1

How about something like this

SELECT  lt.Username, 
        lt.Name, 
        T.Balance, 
        T.Date 
FROM    (
            SELECT  u.UserID,
                    u.UserName,
                    T.TransactionType,
                    TT.Name,
                    MAX(t.Date) LastDate
            FROM    tbUser U INNER JOIN 
                    tbTransaction T ON T.UserId = U.UserId INNER JOIN 
                    tbTransactionType TT ON TT.TypeId = T.TransactionType
            GROUP BY    u.UserID,
                        u.UserName,
                        T.TransactionType,
                        TT.Name
        ) lt INNER JOIN 
        tbTransaction T ON  T.UserId = lt.UserId
                        AND t.TransactionType = lt.TransactionType
                        AND t.Date = lt.LastDate 
WHERE   lt.UserId = 1

The first sub select will return a list of all users and transaction types, and the last transaction date per.

You can then JOIN back to the Transactions table using this information to retrieve the balances.

edited Nov 27, 2010 at 4:46

answered Nov 27, 2010 at 4:40

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

2 Comments

Cybrix Over a year ago

It's working! But is there a way to do it without sub-selects? Tell me if I'm wrong but will the server have trouble running that query as the tbTransaction grows?

Adriaan Stander Over a year ago

I dont think you will be able to do iw without the subselect, unless you have a predetemined number of transaction types to allow for a clever LIMIT statement. If your indexing is correct, it should not be to great a problem.

VoodooChild · Accepted Answer · 2010-11-27 04:40:52Z

0

I would like to get the last two entry of each type of transaction for the UserId 1

If this is what you want then you do have that in your question, unless I misunderstood the question.

You want last two entries for each TransactionType:

-Deposite: you have the Transaction for $200 and $20

-Withdraw: you have the Transaction for $100 and $10

what exactly are you trying to do?

answered Nov 27, 2010 at 4:40

VoodooChild

9,8049 gold badges70 silver badges101 bronze badges

1 Comment

Cybrix Over a year ago

Yes sorry about that. I edited it: I would like to get the last entry of each type of transaction for the UserId 1

Collectives™ on Stack Overflow

MySQL problem with multiple-tables

3 Answers 3

7 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related