SQL Subquery with multiple rows

Question

I have this kind of database with columns: id, date, time, desc, user_id

id|date      |time    |desc|user_id
1 |12-11-1990|09:00:00|In  |1
2 |12-11-1990|18:00:00|Out |1
3 |12-11-1990|09:00:00|In  |2
4 |12-11-1990|18:00:00|Out |2
5 |13-11-1990|10:00:00|In  |1
6 |13-11-1990|17:00:00|Out |1

And I want to know, how to make the table looks like it like this when using query

date      |in      |out     |user_id
12-11-1990|09:00:00|18:00:00|1
12-11-1990|09:00:00|18:00:00|2
13-11-1990|10:00:00|17:00:00|1

Tried to subquery it, but get an error because it returns multiple rows.

SELECT date, (
    SELECT time 
    FROM absensi
    WHERE desc="In"
) AS CHECK_IN, (
    SELECT time 
    FROM absensi
    WHERE desc="Out"
) AS CHECK_OUT
FROM absensi GROUP BY date

Shown DATE values are not legal in MySQL, YYYY-MM-DD must be used. — Akina
– Akina, Commented Mar 29, 2021 at 8:45
It's still unclear what output you want. Edit your question and show your expected output. — Yasir Mushtaq
– Yasir Mushtaq, Commented Mar 29, 2021 at 12:00
thank you for your advice, already edit the expected output. — Ananda Pramono
– Ananda Pramono, Commented Mar 29, 2021 at 23:52

Gordon Linoff · Accepted Answer · 2021-03-29 11:54:28Z

2

I suspect you want one row per user per day. This might be as simple as:

select date, user_id, min(time), max(time)
from t
group by date, user_id;

This assumes that the in is always before the out.

answered Mar 29, 2021 at 11:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ananda Pramono Over a year ago

Thank you for your response, I already edit the expected result for the question. What I want is show the check in and checkout time of each existing user everyday.

Gordon Linoff Over a year ago

@AnandaPramono . . . I think this does what you want.

Ananda Pramono Over a year ago

Thank you very much sir

Akina · Accepted Answer · 2021-03-29 08:44:00Z

0

SELECT dates.`date`, t1.`time` `In`, t2.`time` `Out`
FROM ( SELECT DISTINCT `date`
       FROM absensi ) dates
LEFT JOIN absensi t1 USING (`date`)
LEFT JOIN absensi t2 USING (`date`)
WHERE t1.desc = 'In'
  AND t2.desc = 'Out'

The query assumes that there is no 2 or more rows with the same (date, desc) combination.

answered Mar 29, 2021 at 8:44

Akina

43.2k6 gold badges17 silver badges30 bronze badges

1 Comment

Ananda Pramono Over a year ago

Thank you for your response, But if there are more user, there will be duplicate rows with the same date and desc

Collectives™ on Stack Overflow

SQL Subquery with multiple rows

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related