-1

i have two table in my database

tb_bengkel, this is data of tb_bengkel

+------------+--------------+----------------+--------------+
| id_bengkel | nama_bengkel | alamat_bengkel | id_sparepart |
+------------+--------------+----------------+--------------+
|          1 | sejahtera    | jl psm         | 1,2          |
|          2 | abadi        | jl jakarta     | 1            |
|          3 | toyota       | jl soeta       | 3            |
|          4 | honda        | jl cikapundung | 1,2,3        |
+------------+--------------+----------------+--------------+

tb_ranking, and this is data of tb_ranking

+------------+--------------+------------------+---------------------+---------+--------------+
| id_ranking | id_kendaraan | total            | tanggal             | service | id_sparepart |
+------------+--------------+------------------+---------------------+---------+--------------+
|         79 | 12           | 0.77386363636364 | 2020-05-03 11:01:00 |       0 | 1            |
|         80 | 10           | 0.68516666666667 | 2020-05-03 11:01:00 |       0 | 1,2          |
|         81 | 22           | 0.67926571428571 | 2020-05-05 13:01:00 |       0 | 1,2,3        |
|         82 | 11           | 0.61842857142857 | 2020-05-04 13:01:00 |       0 | 2            |
+------------+--------------+------------------+---------------------+---------+--------------+

i have field id_sparepart in tb_bengkel and tb_ranking, and i want to select data where id_sparepart in tb_bengkel is the contents of id_sparepart in tb_ranking

i try this query, 

select tb_bengkel.id_bengkel,tb_bengkel.nama_bengkel,tb_bengkel.id_sparepart, tb_ranking.id_ranking,tb_ranking.id_sparepart
from tb_bengkel inner join tb_ranking
on tb_bengkel.id_sparepart=tb_ranking.id_sparepart;

the result of query:

+------------+--------------+--------------+------------+--------------+
| id_bengkel | nama_bengkel | id_sparepart | id_ranking | id_sparepart |
+------------+--------------+--------------+------------+--------------+
|          2 | abadi        | 1            |         79 | 1            |
|          1 | sejahtera    | 1,2          |         80 | 1,2          |
|          4 | honda        | 1,2,3        |         81 | 1,2,3        |
+------------+--------------+--------------+------------+--------------+

but the data that should appear is the data in tb_ranking whose id_sparepart is available in tb_bengkel for example, id_ranking 79 has id_sparepart 1 then there should appear 3 in 

+------------+--------------+--------------+------------+--------------+
| id_bengkel | nama_bengkel | id_sparepart | id_ranking | id_sparepart |
+------------+--------------+--------------+------------+--------------+
|          1 | sejahtera    | 1,2          |         79 | 1            |
|          2 | abadi        | 1            |         79 | 1            |
|          4 | honda        | 1,2,3        |         79 | 1            |
+------------+--------------+--------------+------------+--------------+

because id_bengkel has the availability of id_sparepart 1

example two, in id_ranking 82 has id_sparepart 2, then the data that should appear is 

+------------+--------------+--------------+------------+--------------+
| id_bengkel | nama_bengkel | id_sparepart | id_ranking | id_sparepart |
+------------+--------------+--------------+------------+--------------+
|          1 | sejahtera    | 1,2          |         82 | 2            |
|          4 | honda        | 1,2,3        |         82 | 2            |
+------------+--------------+--------------+------------+--------------+

what query should I use?

Irfan F
  • 81
  • 7

1 Answers1

0
select * from tb_ranking join tb_bengkel on tb_bengkel.spare LIKE CONCAT( '%', tb_ranking.spare,'%' ) ORDER BY `tb_ranking`.`rank` ASC

using like on joining condition will give u result based on condition -- assumming the id_sparepart column is a varchar

Madhu
  • 1
  • 2