mysql加入2个表的结果 [英] mysql join 2 table results

问题描述

我有3张桌子:

站点:ID，名称
关联:site_id，assoc_type，assoc_id
选项:ID，名称

Sites: ID, name
Assoc: site_id, assoc_type, assoc_id
Options: ID, name

我想一次执行以下查询

SELECT * FROM sites
    FOREACH site SELECT * FROM assoc WHERE assoc_type='option' AND site_id = site.ID
    FOREACH assoc SELECT name FROM options WHERE ID = assoc.ID

FOREACH SITE
    echo name, array(option 1, option2, option3);

这可能吗?

我要缩短的代码

      $getsites = mysql_query("SELECT * FROM sites")or die(mysql_error());
      while($row = mysql_fetch_array($getsites)){
        echo $row['name'];
        $getassoc = mysql_query("SELECT * FROM assoc WHERE type='options' AND site_id = '$row[ID]'")or die(mysql_error());
        echo'<ul>';
        while($subrow = mysql_fetch_array($getassoc)){
          $getoption = mysql_query("SELECT * FROM options WHERE ID = '$subrow[assoc_id]'")or die(mysql_error());
          $option = mysql_fetch_assoc($getoption);
          echo '<li>'.$option['name'].'</li>';
       }
       echo'</ul><br/>';

       }

推荐答案

SELECT s.name
     , ( SELECT o.name
         FROM Options AS o
           JOIN Assoc AS a
             ON a.assoc_id = o.ID
         WHERE a.site_id = s.id
           AND a.assoc_type='option'
         ORDER BY o.id
         LIMIT 1 OFFSET 0
       ) AS option1
     , ( SELECT o.name
         FROM Options AS o
           JOIN Assoc AS a
             ON a.assoc_id = o.ID
         WHERE a.site_id = s.id
           AND a.assoc_type='option'
         ORDER BY o.id
         LIMIT 1 OFFSET 1
       ) AS option2
     , ( SELECT o.name
         FROM Options AS o
           JOIN Assoc AS a
             ON a.assoc_id = o.ID
         WHERE a.site_id = s.id
           AND a.assoc_type='option'
         ORDER BY o.id
         LIMIT 1 OFFSET 2
       ) AS option3
FROM Sites AS s

如果要对3个表执行通常的联接，要查找所有站点和相关选项，可以使用:

If you want to perform a usual join of the 3 tables, to find all sites and related options, you can use:

SELECT s.name AS site
     , o.name AS option
FROM Sites AS s
    JOIN Assoc AS a
        ON a.site_id = s.id
    JOIN Options AS o
        ON o.ID = a.assoc_id
WHERE a.assoc_type = 'option'
ORDER BY s.name
       , o.name

这篇关于mysql加入2个表的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！