在Mysql中显示来自4（四）个表的数据

Question

我只需要一个简单的查询，

下面是我的桌子：

Information->id_info,year,information_name,person_name,country_id,state_id,city_id

country->id,country_id,country_name

state->id,state_id,country_id,state_name

city->id,city_id,state_id,city_name

我已经运行了一些查询，例如：

                         SELECT * 
                         FROM information, country, state, city 
                         WHERE information.country_id =country.country_id 
                         AND information.state_id = state.state_id
                         AND information.city_id = city.city_id 
                         GROUP BY information.id_info;

我的简单脚本：

echo '<td>'.$data['country_name'].'</td>

echo '<td>'.$data['state_name'].'</td>

echo '<td>'.$data['city_name'].'</td>

$data-> Is my while script with $query=mysql_query...

从上面的查询中只显示了我的数据库中的两（2）个数据，但在数据库中它有五（5）个数据。

然后我尝试删除Group语句，但数据一直循环，显示了近8000个数据，但我只得到表上的5个数据。

我什么都试过了，左接，右接，内接....

我需要帮助，我知道这相当简单，但我怎么能只是正常显示所有的数据。

多谢了。

下面是我显示数据的完整脚本：

<table cellpadding="5" cellspacing="0" border="1">
    <tr bgcolor="#CCCCCC">
        <th>No.</th>
        <th>Year</th>
        <th>Information Name</th>
        <th>Person Name</th>
        <th>Country</th>
        <th>State</th>
        <th>City</th>
        <th>Act</th>
    </tr>

    <?php
    include('connect.php');

    $query = mysql_query("SELECT *
                  FROM information
                  INNER JOIN country ON information.country_id =country.country_id
                  INNER JOIN state ON information.state_id = state.state_id
                                  INNER JOIN city ON information.city_id = city.city_id
                                  GROUP BY information.country_id, information.state_id, information.city_id") or die(mysql_error());


    if(mysql_num_rows($query) == 0){


        echo '<tr><td colspan="6">No data!</td></tr>';

    }else{  


        $no = 1;    
        while($data = mysql_fetch_assoc($query)){   

            echo '<tr>';
                echo '<td>'.$no.'</td>';    
                echo '<td>'.$data['year'].'</td>';  
                echo '<td>'.$data['information_name'].'</td>';  
                echo '<td>'.$data['person_name'].'</td>';   
                echo '<td>'.$data['country_name'].'</td>';  
                echo '<td>'.$data['state_name'].'</td>';    
                echo '<td>'.$data['city_name'].'</td>'; 
                echo '<td><a href="viewinfo.php?id='.$data['id_info'].'">Detail</a> / <a href="edit.php?id='.$data['id_info'].'">Edit</a> / <a href="delete.php?id='.$data['id_info'].'" onclick="return confirm(\'Are You Sure?\')">Delete</a></td>';  
            echo '</tr>';

            $no++;  

        }

    }
    ?>

Answer 1

您可以使用INNER JOIN，前提是索引键使用正确：

SELECT country.country_name,
       state.state_name,
       city.city_name
FROM information
INNER JOIN country ON information.country_id = country.country_id
INNER JOIN state ON information.state_id = state.state_id
INNER JOIN city ON information.city_id = city.city_id

Answer 2

尝试更改组,并使用内部连接：

  SELECT *
  FROM information
   INNER JOIN country ON information.country_id =country.country_id
   INNER JOIN state ON information.state_id = state.state_id
   INNER JOIN city ON information.city_id = city.city_id
   GROUP BY information.country_id, information.state_id, information.city_id