php - 从 3 个表中获取数据

Question

我有这些 SQL 表

gouvernorat
id  |nom
1   TUNIS
2   ARIANA
3   BEN AROUS
4   MANOUBA
5   NABEUL

delegation
id|id_gouvernorat| nom
1   1              EL MENZAH
2   1              EL HRAIRIA
3   1              EL KABBARIA
22  2              RAOUADE
23  2              SIDI THABET

id_gouvernorat 是 gouvernorat 的外键

tarifs_zone
zone_a|zone_b|prix
1      2      10
1      3      15
1      4      17
1      5      0
2      3      1
2      4      5

zone_a和zone_b是委托(delegate)的外键

我要找回zone_a 的 gouvernorat.nom,delegation.nom，zone_b 的 gouvernorat.nom,delegation.nom 以及没有重复的价格(A,B 没有 B,A)

public function Get_zones($start, $length, $order, $dir,$search,$gouvernorat){
$sql='
SELECT (

SELECT delegation.id_gouvernorat 
FROM delegation 
WHERE tarifs_zones.zone_b=delegation.id) 
AS zoneB, 

gouvernorat.nom AS gouvernoratA, 
gouvernorat.id AS zoneA, 
delegation.nom AS delegationA, 
tarifs_zones.prix AS prix,

(SELECT gouvernorat.nom 
from gouvernorat 
WHERE zoneB=gouvernorat.id) 
AS gouvernoratB,

(SELECT delegation.nom 
FROM delegation 
WHERE tarifs_zones.zone_b=delegation.id) 
AS delegationB

FROM tarifs_zones 

JOIN delegation ON delegation.id = tarifs_zones.zone_a 
JOIN gouvernorat ON delegation.id_gouvernorat = gouvernorat.id 

HAVING (gouvernoratA= \''.$gouvernorat.'\' OR gouvernoratB= \''.$gouvernorat.'\' OR \'Tous les gouvernorats\'=\''.$gouvernorat.'\') 
AND (delegationA LIKE \'%'.$search.'%\' OR gouvernoratA LIKE \'%'.$search.'%\' OR delegationB 

LIKE \'%'.$search.'%\' OR gouvernoratB LIKE \'%'.$search.'%\' OR prix LIKE \'%'.$search.'%\') 

ORDER BY '.$order.' '.$dir.' 
LIMIT '. $start.', '.$length.';
';

return $this->db->query($sql);
}

这是我的功能，但我觉得它太复杂了，我无法找到一种方法来根据两个 gouvernorat.nom 更新所有区域的价格

Answer 1

SELECT 后面不需要使用子查询。您不应该害怕在 JOIN 中使用同一个表两次。在这种情况下，您只需要分配别名即可。

因此您的查询将类似于

 SELECT d2.id_gouvernorat AS zoneB, 
        g1.nom AS gouvernoratA, 
        g1.id AS zoneA, 
        d1.nom AS delegationA, 
        tarifs_zones.prix AS prix,
        d2.nom AS gouvernoratB,
        d2.nom AS delegationB
 FROM tarifs_zones 
 LEFT JOIN delegation d1 ON d1.id = tarifs_zones.zone_a     // delegation with alias d1 for zone_a
 LEFT JOIN delegation d2 ON d2.id = tarifs_zones.zone_b     // delegation with alias d2 for zone_b
 LEFT JOIN gouvernorat g1 ON d1.id_gouvernorat = g1.id 
 LEFT JOIN gouvernorat g2 ON d2.id_gouvernorat = d2.id 
 HAVING (gouvernoratA= \''.$gouvernorat.'\' OR gouvernoratB= \''.$gouvernorat.'\' OR \'Tous les gouvernorats\'=\''.$gouvernorat.'\') 
      AND (delegationA LIKE \'%'.$search.'%\' OR gouvernoratA LIKE \'%'.$search.'%\' OR delegationB 
      LIKE \'%'.$search.'%\' OR gouvernoratB LIKE \'%'.$search.'%\' OR prix LIKE \'%'.$search.'%\') 
 ORDER BY '.$order.' '.$dir.' 
 LIMIT '. $start.', '.$length.';