mysql, multiple table Joins with WHERE clause

Question

I'm using Mysql and I difficult time trying to get the results from a SELECT query. I am having 2 tables. First table jam and second table jadwalblok. data in this table is static:

select * from jam
idjam nmjam
01    09.00-09.50
02    10.00-10.50
03    11.00-11.50
04    12.00-13.00   

select * from jadwalblok
idjadwal idjam ruang tgl
1        01    601   2017-04-24
2        03    602   2017-04-25

I used joins to get results as.

SELECT jam.idjam,
       jam.nmjam,
       jadwalblok.idruang,
       jadwalblok.tgl
FROM jam
LEFT JOIN jadwalblok ON jadwalblok.idjam = jam.idjam
WHERE jadwalblok.tgl='2017-04-24'

But I am not getting correct result. I want results as shown below:

idjam nmjam        ruang tgl
01    09.00-09.50  601   2017-04-24
02    10.00-10.50  null  null
03    11.00-11.50  null  null
04    12.00-13.00  null  null

Next time pls show the results you get when you run your query, that's a huge help. In this particular case I could determine what had gone wrong, but this may not be the case for a more complicated query. — Shadow
– Shadow, Commented Jan 16, 2017 at 1:35

Shadow · Accepted Answer · 2017-01-16 01:33:31Z

2

You need to move the jadwalblok.tgl='2017-04-24' criterion from the where clause to the join condition because the where clause is applied after the 2 tables are joined, while the join condition is applied during the join:

SELECT jam.idjam,
       jam.nmjam,
       jadwalblok.idruang,
       jadwalblok.tgl
FROM jam
LEFT JOIN jadwalblok ON jadwalblok.idjam = jam.idjam and jadwalblok.tgl='2017-04-24'

answered Jan 16, 2017 at 1:33

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

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

Collectives™ on Stack Overflow

mysql, multiple table Joins with WHERE clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related