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

Mysql – how to join table if i want data null

i want to join table, and i have 2 table like

Table : Suhu

ID_ROOM | SUHU_MIN | SUHU_MAX
----------------------------------
   1        10           15
   1        12           20
   1        15           25
   1        20           22

Table : RH

ID_ROOM | RH_MIN | RH_MAX
----------------------------------
   1        11       19
   1        14       18

and I want output like this :

ID_ROOM | SUHU_MIN | SUHU_MAX   | RH_MIN  | RH_MAX
----------------------------------------------------
   1        10           15          11        19
   1        12           20          14        18
   1        15           25          NULL      NULL
   1        20           22          NULL      NULL

I have used left join, and the result is like this

SELECT S.suhu_min, S.suhu_max, R.rh_min, R.rh_max
FROM SUHU S LEFT JOIN RH R ON S.ID_ROOM = R.ID_ROOM;

ID_ROOM | SUHU_MIN | SUHU_MAX   | RH_MIN  | RH_MAX
----------------------------------------------------
   1        10           15          11        19
   1        12           20          14        18
   1        15           25          11        19
   1        20           22          14        18
Tags:

2

Answers


  1. 0

    I assume you want to compare min_value and max_value

    SELECT S.suhu_min, S.suhu_max, R.rh_min, R.rh_max 
FROM SUHU S 
LEFT JOIN RH R ON S.ID_ROOM = R.ID_ROOM AND S.suhu_min<R.rh_min AND S.suhu_max<R.rh_max
    Login or Signup to reply.
  2. 0

    Here is the SQL query that you can use

    But there must be a logic that differs one row from others

    I sorted the rows by _MIN columns per ID_Room and joined two tables accordingly

    select
t1.ID_ROOM,
t1.SUHU_MIN,
t1.SUHU_MAX,
t2.RH_MIN,
t2.RH_MAX
from (
select 
ID_ROOM,
ROW_NUMBER() over (partition by ID_ROOM order by SUHU_MIN) as rn,
SUHU_MIN,
SUHU_MAX
from suhu
) t1
left join (
select
ID_ROOM,
ROW_NUMBER() over (partition by ID_ROOM order by RH_MIN) as rn,
RH_MIN,
RH_MAX
from rh
) t2 on t1.ID_ROOM = t2.ID_ROOM
and t1.rn = t2.rn
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search