MYSQL根据修剪后的id拉取5张表上的搜索数据

Question

我不是专家，我尝试在 4 个表上进行搜索，这取决于它的修剪项目 ID，例如 http://sqlfiddle.com/#!2/6014c/4/0

SELECT item.str_kd_induk_item, 
    (
        CASE 
        WHEN SUBSTRING(item.str_kd_induk_item,8,1) = '1' THEN (SELECT str_judul_asli FROM buku_judul WHERE str_kd_judul=item.str_kd_judul)
        WHEN SUBSTRING(item.str_kd_induk_item,8,1) = '2' THEN (SELECT str_judul_asli FROM penelitian_judul WHERE str_kd_judul=item.str_kd_judul)
        WHEN SUBSTRING(item.str_kd_induk_item,8,1) = '3' THEN (SELECT str_judul_asli FROM majalah_judul WHERE str_kd_judul=item.str_kd_judul)
        WHEN SUBSTRING(item.str_kd_induk_item,8,1) = '5' THEN (SELECT str_judul_asli FROM ta_judul WHERE str_kd_judul=item.str_kd_judul)        
        WHEN SUBSTRING(item.str_kd_induk_item,8,1) = '6' THEN (SELECT str_judul_asli FROM jurnal_judul WHERE str_kd_judul=item.str_kd_judul)
        END) AS judul,koleksi.str_nm_koleksi,sumber.str_nm_sumber
FROM item,sumber,koleksi WHERE koleksi.str_kd_koleksi=SUBSTRING(item.str_kd_induk_item,8,1) AND sumber.int_id_sumber=item.id_sumber AND judul='%a%';

我无法搜索虚拟表 judul，因为它无法识别任何人有更好的解决方案？

Answer 1

在 MySQL 中，您可以使用 having 子句。您还应该学习正确的显式 join 语法:

SELECT item.str_kd_induk_item, 
    (
        CASE 
        WHEN SUBSTRING(item.str_kd_induk_item,8,1) = '1' THEN (SELECT str_judul_asli FROM buku_judul WHERE str_kd_judul=item.str_kd_judul)
        WHEN SUBSTRING(item.str_kd_induk_item,8,1) = '2' THEN (SELECT str_judul_asli FROM penelitian_judul WHERE str_kd_judul=item.str_kd_judul)
        WHEN SUBSTRING(item.str_kd_induk_item,8,1) = '3' THEN (SELECT str_judul_asli FROM majalah_judul WHERE str_kd_judul=item.str_kd_judul)
        WHEN SUBSTRING(item.str_kd_induk_item,8,1) = '5' THEN (SELECT str_judul_asli FROM ta_judul WHERE str_kd_judul=item.str_kd_judul)        
        WHEN SUBSTRING(item.str_kd_induk_item,8,1) = '6' THEN (SELECT str_judul_asli FROM jurnal_judul WHERE str_kd_judul=item.str_kd_judul)
        END) AS judul,koleksi.str_nm_koleksi,sumber.str_nm_sumber
FROM item JOIN
     sumber
     ON sumber.int_id_sumber = item.id_sumber JOIN
     koleksi
     ON koleksi.str_kd_koleksi = SUBSTRING(item.str_kd_induk_item, 8, 1)
HAVING judul = '%a%';