从3个表中获取数据

8gsdolmq 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(211)

我有这些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\u 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

区域a和区域b是委派的外键
我要检索区域a的gouvernorat.nom，delegation.nom，区域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

sql mysql php codeigniter

来源：https://stackoverflow.com/questions/48319415/get-data-with-from-3-tables

1条答案

按热度按时间

vuktfyat1#

不需要在后面使用子查询 SELECT . 你不应该害怕在同一张table上用两次 JOIN s。在这种情况下，您只需要指定别名。
因此，您的查询将类似于

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.';

赞(0）回复(0）举报 2021-06-21

我来回答

从3个表中获取数据

1条答案

相关问题

热门标签

最新问答