MySQL problem with multiple-tables

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`.`tbTransact开发者_高级运维ionType` (

  `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!

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)

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.

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?