Query to select values from the same column as given values, but different rows-CodePudding

I have a table for a school's time table, as follows, where p1..p4 are 'periods' The values of periods p1,p2,p3,p4 in the first row contain 'timings' of subjects s1,s2,s3,... on certain days. It is required to return a list of all the timings of a given list of subjects on a particular day.

assume all fields are strings

---------t_time--------
    day  p1  p2  p3  p4
0   null 4   5   6   7
1   mon  s1  s2  s3  s4
2   tue  s2  s3  s4  s5
3   wed  s3  s4  s5  s1
4   mon  s2  s4  s3  s1

For example, for day = ['mon','wed'] sub = ['s1','s3'], the output would be as such:

  day sub time
0 mon s1  4
1 mon s3  6
3 wed s3  4
4 wed s1  7
5 mon s1  7

Until now I am using a very inefficient search loop in php|mysqli and want to change it.

I thought separating the 0th row as its own table which would make more sense, but apparently its a constraint i have got to work with

---------t_time--------
    day  p1  p2  p3  p4
0   mon  a   b   c   d
1   tue  b   e   a   b
2   wed  p   q   d   e
3   mon  m   n   o   p

---------p_time--------
    period  time
0   p1      4
1   p2      5
2   p3      6
3   p4      7

But in this case I would somehow have to query the column_names of columns that contained particular values in a row, which I cannot understand.

CodePudding user response：

Join each row with a class with the null row to get the time of that class. You have to do this separately for each period column, then union them all together to get the final result.

SELECT t1.day, t1.p1 AS sub, t2.p1 AS time
FROM t_time AS t1
CROSS JOIN t_time AS t2
WHERE t1.day IN ('mon', 'wed') AND t1.p1 IN ('s1', 's3')
AND t2.day IS NULL

UNION

SELECT t1.day, t1.p2, t2.p2
FROM t_time AS t1
CROSS JOIN t_time AS t2
WHERE t1.day IN ('mon', 'wed') AND t1.p2 IN ('s1', 's3')
AND t2.day IS NULL

UNION

SELECT t1.day, t1.p3, t2.p3
FROM t_time AS t1
CROSS JOIN t_time AS t2
WHERE t1.day IN ('mon', 'wed') AND t1.p3 IN ('s1', 's3')
AND t2.day IS NULL

UNION

SELECT t1.day, t1.p4, t2.p4
FROM t_time AS t1
CROSS JOIN t_time AS t2
WHERE t1.day IN ('mon', 'wed') AND t1.p4 IN ('s1', 's3')
AND t2.day IS NULL

DEMO

This would be much simpler if the data were normalized, with each period in a separate row, as well as separating the tables as you mention. But if you're stuck with this, you need separate queries for each period, essentially pivoting the columns into rows.