SQL: How to show the WHERE IN and WHERE NOT IN in the same line?

Question

Good morning masters.

Basically i want to show 3 tables. But i have some condition:

• show table perangkat,jenis_perangkat,pasang but only show the data with ID that has been saved on table sedia (those 3 tables above have the same ID named kd_jp and has been saved on sedia) AND DON'T SHOW the data with ID that has been saved on pasang.

After run the code above, i got syntax error. It is working until this :

SELECT  `perangkat`.`kd_jp`,  `perangkat`.`no_seri`,  `perangkat`.`status`,  
        `jenis_perangkat`.`nm_jp`,  `jenis_perangkat`.`merk_jp`
FROM`perangkat`
INNER JOIN `jenis_perangkat` 
ON `jenis_perangkat`.`kd_jp` = `perangkat`.`kd_jp` 
WHERE perangkat.kd_jp IN (select sedia.kd_jp from sedia) 

BUT it's not working when i add the NOT IN : and where perangkat.kd_jp not in (select pasang.kd_jp from pasang);

So here is the Query that got Syntax Error:

SELECT  `perangkat`.`kd_jp`,  `perangkat`.`no_seri`,  `perangkat`.`status`,  
        `jenis_perangkat`.`nm_jp`,  `jenis_perangkat`.`merk_jp`
FROM`perangkat`
INNER JOIN `jenis_perangkat` 
ON `jenis_perangkat`.`kd_jp` = `perangkat`.`kd_jp` 
WHERE perangkat.kd_jp IN (select sedia.kd_jp from sedia) 
  AND where perangkat.kd_jp not in (select pasang.kd_jp from pasang);

Answer 1

You can only have one WHERE clause. Change WHERE ... AND WHERE ... to WHERE ... AND ...:

SELECT `perangkat`.`kd_jp`, `perangkat`.`no_seri`, `perangkat`.`status`,
  `jenis_perangkat`.`nm_jp`, `jenis_perangkat`.`merk_jp` 
FROM `perangkat`
INNER JOIN `jenis_perangkat`
  ON `jenis_perangkat`.`kd_jp` = `perangkat`.`kd_jp`
WHERE perangkat.kd_jp IN (SELECT sedia.kd_jp FROM sedia)
  AND perangkat.kd_jp NOT IN (SELECT pasang.kd_jp FROM pasang);

Answer 2

you cant use and where:

and where perangkat.kd_jp not in (select pasang.kd_jp from pasang);

just use and:

and where perangkat.kd_jp not in (select pasang.kd_jp from pasang);

SQL sintaxe select sintaxe is

select * from [table] WHERE ... AND ... AND ... OR ...

So

SELECT  `perangkat`.`kd_jp`,  `perangkat`.`no_seri`,  `perangkat`.`status`,  `jenis_perangkat`.`nm_jp`,  `jenis_perangkat`.`merk_jp`FROM`perangkat`INNER JOIN `jenis_perangkat` ON `jenis_perangkat`.`kd_jp` = `perangkat`.`kd_jp` where perangkat.kd_jp in (select sedia.kd_jp from sedia) and perangkat.kd_jp not in (select pasang.kd_jp from pasang);

UPDATE

A better solution is use EXISTS instead of IN and NOT IN

SELECT a.kd_jp, a.no_seri, a.status, b.nm_jp, b.merk_jp 
FROM perangkat a 
INNER JOIN jenis_perangkat b  ON b.kd_jp = a.kd_jp 
WHERE EXISTS (SELECT c.kd_jp FROM sedia c WHERE c.kd_jp = a.kd_jp) 
AND NOT EXISTS (SELECT c.kd_jp FROM pasang c WHERE c.kd_jp = a.kd_jp);