When joining the same table, how to put the joined data in separate rows rather than separate column-CodePudding

Let us say, I have a query like the following.

select * from `Students` s1
join `Students` s2
on s2.id = s1.id   1 and s2.marks >= 80 and s1.marks >= 80

In this case, the data for s1 and s2 would be put into two separate columns, but how can I put them into separate rows instead?.

Here is a sample data:

 --- ------ 
| id| marks| 
 --- ------|
| 1 | 88   |
| 2 | 77   |
| 3 | 81   |
| 4 | 82   |
| 5 | 75   |
| 6 | 80   |
| 7 | 90   |
| 8 | 93   |
 --- ------

What I want is rows (ordered by id) with two consecutive 80 marks to be shown. For example, in this case, rows with id 3, 4, 6, 7 and 8 would be shown.

CodePudding user response：

If your version of MySql is 8.0 you can use LAG() and LEAD() window functions to check for the previous and next values of marks of each row:

SELECT id, marks
FROM (
  SELECT *,
         LAG(marks) OVER (ORDER BY id) prev_marks,
         LEAD(marks) OVER (ORDER BY id) next_marks
  FROM students       
) t
WHERE marks >= 80 AND (prev_marks >= 80 OR next_marks >= 80)
ORDER BY id;

For previous versions use EXISTS:

SELECT s1.*
FROM students s1
WHERE s1.marks >= 80
AND EXISTS (
  SELECT 1 
  FROM students s2
  WHERE s2.id IN (s1.id - 1, s1.id   1) AND s2.marks >= 80
)
ORDER BY s1.id;

Or, with a self join:

SELECT DISTINCT s1.*
FROM students s1 INNER JOIN students s2
ON s2.id IN (s1.id - 1, s1.id   1)
WHERE s1.marks >= 80 AND s2.marks >= 80
ORDER BY s1.id;

See the demo.

CodePudding user response：

As stated without seeing your data what I guess is that you can use union in your query

SELECT
  s1.*
FROM
  `students` s1
  JOIN students s2
    ON s2.id = s1.id   1
    AND s2.marks >= 80
    AND s1.marks >= 80
UNION
SELECT
  s2.*
FROM
  `students` s1
  JOIN students s2
    ON s2.id = s1.id   1
    AND s2.marks >= 80
    AND s1.marks >= 80
ORDER BY id;