CODEWITHSUNDEEP
mysqljoin
AQanitah
AQanitah

Reputation: 3

mysql, multiple table Joins with WHERE clause

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

Upvotes: 0

Views: 78

Answers (1)

Shadow
Shadow

Reputation: 34232

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'

Upvotes: 2

Related Questions