从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是委托的外键

我想检索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.';