Question 217079 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Select the matched row plus next 3 rows followed

Table students has 3 columns id, name, and class.

I want to select the matched row based on the condition for example if the student’s name is Jhon, plus the next 3 rows (without any condition) followed by the match row in just one query.

After research, my code below returns only 1 row, or maybe this is not the way of doing what I want:

SELECT * FROM `students` AS `parent`

INNER JOIN `students` AS `child`

WHERE `parent`.`name` = ? AND `child`.`id` > `parent`.`id`
 
ORDER BY `child`.`id` ASC LIMIT 3

Note: I am using PHP Prepared Statements.

Tags:

2

Answers


  1. 0

    I think you might need to change your join criteria from > to >= and select from the child table instead:

    SELECT `child`.*
FROM `students` AS `parent`
INNER JOIN `students` AS `child` ON `child`.`id` >= `parent`.`id`
WHERE `parent`.`name` = ?
ORDER BY `child`.`id` ASC
LIMIT 3
    Login or Signup to reply.
  2. 0

    Try to use this, the UNION ALL operator combines the two result sets.

    SELECT * FROM (
  SELECT * FROM students WHERE name = ?
  UNION ALL
  SELECT * FROM students WHERE id > (
    SELECT id FROM students WHERE name = ? LIMIT 1
  ) ORDER BY id LIMIT 3
) AS result
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search