CODEWITHSUNDEEP
phpmysql
john BB
john BB

Reputation: 79

MySQL. Select last record (by created time) from second table

I have 2 tables links by userPhone.

Table users

+-----------+-------+----------+--------+
| userPhone |  name | address  |  car   |
+-----------+-------+----------+--------+
| 096111111 | Bill  |  adr1    | {json} |
+-----------+-------+----------+--------+
| 097333333 | Max   |  adr2    | {json} |
+-----------+-------+----------+--------+
| 098888888 | Denis |  adr3    | {json} |
+-----------+-------+----------+--------+

Table Orders

+-----------+---------+-------+-------+------------+
| userPhone | orderID | title | Descr | createdTS  |
+-----------+---------+-------+-------+------------+
| 096111111 | 59      | ttl1  | qqqq  | 1444999740 |
+-----------+---------+-------+-------+------------+
| 096111111 | 58      | ttl2  | wwww  | 1444999650 |
+-----------+---------+-------+-------+------------+
| 096111111 | 56      | ttl3  | rrrrr | 1444999600 |
+-----------+---------+-------+-------+------------+
| 096111111 | 57      | ttl4  | ttttt | 1444999540 |
+-----------+---------+-------+-------+------------+

I'm using request like that, and it works well:

SELECT
      `users`.`userPhone`,
      `users`.`name`,
      `users`.`address`,
      `users`.`car`,
      (SELECT `title` FROM `orders` WHERE `orders`.`userPhone` = `users`.`userPhone` AND `orders`.`orderTS` < NOW()  ORDER BY `orders`.`createdTS` DESC LIMIT 1 ) as `title`,
      (SELECT `descr` FROM `orders` WHERE `orders`.`userPhone` = `users`.`userPhone` AND `orders`.`orderTS` < NOW()  ORDER BY `orders`.`createdTS` DESC LIMIT 1 ) as `descr`
FROM
              `users`
               LEFT JOIN `orders` USING (`userPhone`)
WHERE 
               `users`.`userPhone` like '%1111%';

BUT I think additional SELECTs work slow. Is there a better and faster way to solve it?

How to get same result using another request (probably without additional SELECTs).

Upvotes: 0

Views: 72

Answers (2)

RiggsFolly
RiggsFolly

Reputation: 94672

A correctly syntaxed JOIN will do all that for you, without the need for the inner SELECT's and much quicker.

SELECT
      `users`.`userPhone`,
      `users`.`name`,
      `users`.`address`,
      `users`.`car`,
      `orders`.`title`,
      `orders`.`desc`
FROM `users`
     JOIN `orders` ON `orders`.`phone` = `users`.`phone`
WHERE 
      `users`.`userPhone` like '%1111%'
  AND `orders`.`orderTS` < NOW() 
ORDER BY `orders`.`orderTS` DESC
LIMIT 1;

If you want all these record then just remove the LIMIT 1

Upvotes: 1

Lucarnosky
Lucarnosky

Reputation: 514

You don't need to JOIN or LEFT JOIN any table if you're using an inner SELECT

Try this

SELECT
      `users`.`userPhone`,
      `users`.`name`,
      `users`.`address`,
      `users`.`car`,
      (SELECT `title` FROM `orders` WHERE `orders`.`userPhone` = `users`.`userPhone` AND `orders`.`orderTS` < NOW()  ORDER BY `orders`.`createdTS` DESC LIMIT 1 ) as `title`,
      (SELECT `descr` FROM `orders` WHERE `orders`.`userPhone` = `users`.`userPhone` AND `orders`.`orderTS` < NOW()  ORDER BY `orders`.`createdTS` DESC LIMIT 1 ) as `descr`
FROM
              `users`
WHERE 
               `users`.`userPhone` like '%1111%';

This should be faster

Upvotes: 0

Related Questions