MySql query with left join and Inner Join on the result

Question

I have two tables in MySql one is users second is users transactions, transactions are related to user by user.id = transaction.user_id not all users have transaction, there is no limit of transaction per user all transactions have added_date field

What I need is a query that will give me list of all users with joined data about their last added transaction (one last transaction), or null's if they dont have any transaction

So what I think is

getting all users left join transactions on user.id = transaction.user_id inner join to get max transaction.added_date grouped by id

something like:

SELECT u.id, 
       u.name, 
       t.added_date
  FROM `user` u 
                LEFT JOIN `transaction` t 
                          ON u.id = t.user_id
               INNER JOIN (
                           SELECT id, 
                                  MAX(added_date) AS addedDate
                             FROM `transaction`
                         GROUP BY id
                           )gt
                          ON t.id = gt.id AND t.added_date = gt.addedDate

but it's giving me not what I expected, it leftjoins the transactions to users but i still have many transactions for same user in the result set

what am I doing wrong?

edit:

I simplified them a little but that should be enough so:

the user

CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(256) NOT NULL,
`email` varchar(128) NOT NULL,
`role_id` mediumint(8) unsigned NOT NULL,
`phone` varchar(128) NOT NULL, 
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

the transaction

    CREATE TABLE `transaction` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,  
  `added_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  PRIMARY KEY (`id`),
  KEY `fk_transaction_user1_idx` (`user_id`),
  CONSTRAINT `transaction_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)     ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I need a list of all users with their last transaction data, if the don't have then nulls

What happens if you substitute `ON t.id = gt.id AND t.added_date = gt.addedDate` with `ON (t.id = gt.id AND t.added_date = gt.addedDate)` ? — Alexander, Feb 06 '14 at 09:25
I've added parentheses as you sugested and results look the same — olechafm, Feb 06 '14 at 09:30

score 0 · Answer 1 · edited Feb 06 '14 at 10:22

Please try this

SELECT u.id, 
       u.name, 
       t.added_date 
  FROM `users` u 
       LEFT JOIN `transaction` t 
                 ON u.id = t.user_id AND t.added_date = (
                                         SELECT MAX( added_date ) 
                                           FROM `transaction` t 
                                          WHERE t.user_id = u.id
                                         )

score 0 · Accepted Answer · edited May 23 '17 at 12:11

0

Thy this

  SELECT user.name, MAX(transaction.added_date) 
    FROM user 
              LEFT JOIN transaction 
                         ON user.id = transaction.user_id
GROUP BY user.name;

Here is SQL Fiddle

Also, try to avoid subqueries in favor of JOINs, because the subqueries usually not good on perfomance

UPD. SQL Fiddle, with sorting ASC by date and NULLs last, using COALESCE
UPD2. Another SQL Fiddle with count

edited May 23 '17 at 12:11

Community

1
1

answered Feb 06 '14 at 10:08

Alexander

3,129
2
19
33

Rakesh thanks for the answer, but on standard 1000 rows limit the execution took over 35sec and fetching another 25 so thats far over accepted performance – olechafm Feb 06 '14 at 10:35
Alexander it worked great I'm really suprised that it was so simple, I will double check the results if they are correct and will vote it up. One more thing what will be the best way to kick the null values to the bottom of results list when ORDER BY ASC? – olechafm Feb 06 '14 at 10:38
like this? order by case when MyDate is null then 1 else 0 end, MyDate – olechafm Feb 06 '14 at 10:39
no, you see, I want to sort results by added_date byt ASC that makes the nulls first, I want them to be on the end so not as within normal ASC null, null, null, 1,2,3 I need them to be sorted like 1,2,3, null, null, null (nulls are representing user records that dont have transactions joined) – olechafm Feb 06 '14 at 11:27
100%, great to have you here Alexander, thanks A LOT! I voted up your answear – olechafm Feb 06 '14 at 11:44
sorry, can't, my reputation is < 15, thats the rule so i will do it when I will reach it... taking the occasion, I will ask for something more, when I will fetch the results of query I can count them but of course It'll be better for that to use a separate query with COUNT not fetching all recors, how should I modify it, to count the results, as I tried to put COUNT(u.id) AS count simply aftere r the select, the results don't match – olechafm Feb 06 '14 at 12:54
getting back to the COUNT, your solution just adds another column to the result set and displays 1 for all the rows, I just need a classic COUNT that will tell me how many records the query will return – olechafm Feb 06 '14 at 13:14
I lied, it adds not only '1' diferent records have diferent values, so I assume that it counst number of records from transaction table that fit the left join rule, before MAX on date and group on ID, I just want to know how many records will the query return, for the needs of paginator that manages results fetching with LIMIT and OFFSET for pages – olechafm Feb 06 '14 at 13:36
I don't see how you achieve this with one query only. I think you should achieve this outside of SQL. also, I suggest we delete all comments here - I refreshed the answer, so everything is there, but the comments look like a chat – Alexander Feb 06 '14 at 14:27
ok I will clear the comments after we will finish, I will do two separate queries on for COUNT ONLY second to fetch the data, but I dont know how tu put count properly in this query so it will count the results instead fetching rows and all the data, after I will count them I will query the db once again to fetch rows with fields – olechafm Feb 06 '14 at 14:37

MySql query with left join and Inner Join on the result

2 Answers2