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

Pivot MySQL table and take missed values from other table

There are two MySQL tables:

table1

id |  time |  status
-----------------------
1  | 10:00 | conn     | 
1  | 10:01 | disconn  | 
2  | 10:02 | conn     | 
2  | 10:03 | disconn  | 
3  | 10:04 | conn     | 


table2

id |  time |
------------
3  | 10:05 |

If there is no disconn time value for ceratin id then take it from table2.
What is sql query to get wished result:

id | conn | disconn|
--------------------
1  | 10:00| 10:01  |
2  | 10:02| 10:03  |
3  | 10:04| 10:05  |
Tags:

2

Answers


  1. 0

    You can use LEFT JOIN and COALESCE for this case. Here is sample code:

    SELECT 
    t1.id,
    t1.time AS conn,
    COALESCE(t1_disconn.time, t2.time) AS disconn
FROM table1 t1
LEFT JOIN table1 t1_disconn ON t1.id = t1_disconn.id AND t1_disconn.status = 'disconn'
LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE t1.status = 'conn'
ORDER BY t1.id;

    Here is the sample output:

    enter image description here

    Here is fiddle link

    Login or Signup to reply.
  2. 0

    Alternatively, You can also use GROUP BY without self join as follows:

    SELECT
    T1.ID,
    MAX(CASE WHEN T1.STATUS = 'conn' THEN T1.TIME END) AS CONN,
    COALESCE(MAX(CASE WHEN T1.STATUS = 'disconn' THEN T1.TIME END), T2.TIME) AS DISCONN
  FROM
    table1 T1
      LEFT JOIN table2 T2 ON T1.ID = T2.ID
 GROUP BY T1.ID, T2.TIME
 ORDER BY T1.ID;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search