how to display data using where condition in mysql-CodePudding

I have a problem that I want to display data using use conditions by this column in ON clause for status_riwayat=keluar AND display data that status_riwayat=masuk not using colomn tanggal conditin like below.

table supplier

CREATE TABLE pj_detailsupplier (
id_detailsupplier int,
id_barang int,
id_supplier int,
no_batch varchar(255),
stok int,
tanggal date,
tgl_expired date,
status_detailsupplier int );

  INSERT INTO `pj_detailsupplier`(`id_detailsupplier`, `id_barang`, `id_supplier`, `no_batch`, `stok`, `tanggal`, `tgl_expired`, `status_detailsupplier`) VALUES ('13208','205','25','1','12','2022-01-01','2022-01-01','0')

table history

 CREATE TABLE pj_riwayat (
 id_riwayat int,
 id_detailsupplier int,
 jumlah int,
 keluar int,
 sisa int,
 tanggal date,
 status_riwayat varchar(255) );

 INSERT INTO `pj_riwayat`(`id_riwayat`, `id_detailsupplier`, `jumlah`, `keluar`, `sisa`, `tanggal`, `status_riwayat`) VALUES ('','13208','12','0','12','2022-09-02','masuk'), ('','13208','0','2','10','2022-11-14','keluar')

I want to display data from status_riwayat=masuk and status_riwayat=keluar but using where condition

I have tried my code

  SELECT * FROM pj_riwayat
  LEFT JOIN pj_detailsupplier ON pj_riwayat.id_detailsupplier=pj_detailsupplier.id_detailsupplier 
  AND month(pj_riwayat.tanggal)=11 AND year(pj_riwayat.tanggal)=2022 
  WHERE pj_detailsupplier.id_barang=205

but this code is still false because I use conditions by this column in ON clause. I wish to display data that status_riwayat=masuk AND status_riwayat=keluar

the result of my code only display one data

I want the desire result is display two data like below by using my query above

CodePudding user response：

It seems that you need in this:

SELECT t2.*, t3.*
          -- find id_detailsupplier which matches 
          -- id_barang=205 and tanggal=2022-11-XX
FROM pj_riwayat t1
JOIN pj_detailsupplier t2
  ON t1.id_detailsupplier=t2.id_detailsupplier 
          -- select all rows with found id_detailsupplier
          -- from another table copy
JOIN pj_riwayat t3
  ON t1.id_detailsupplier=t3.id_detailsupplier 
WHERE t2.id_barang=205
  AND month(t1.tanggal)=11
  AND year(t1.tanggal)=2022 
  AND t3.status_riwayat IN ('masuk', 'keluar')

https://dbfiddle.uk/upv6Kc4A