MySQL Left加入具有根本不同结构的表

I have a mysql query, that already left joins another table and that works fine. I now had to left join yet another table. At first this seemed to work too. But the thing is: The value i'm matching the join on is in one table singulary/indexical, but on the other table it emerges multiply. I'm very bad at articulating this, even tho it's a rather simple set up.

我有一个mysql查询,已经离开加入另一个表,并且工作正常。我现在不得不离开加入另一张桌子。起初,这似乎也有效。但问题是:我匹配连接的值是在一个表中奇异/索引,但在另一个表上它出现了多次。我很难说明这一点,即使这是一个相当简单的设置。

So i made a fiddle to visualize what i mean: https://jsfiddle.net/fesoLvy4/2/

所以我想了解我的意思:https://jsfiddle.net/fesoLvy4/2/

When i now do my usual left join routine and put the mysql_query into PHP:

当我现在做我通常的左连接例程并将mysql_query放入PHP:

<?php
echo "  <table>
          <tr>
            <th>Dog</th>
            <th>Species</th>
            <th>Properties</th>
          </tr>";

     while($data = mysql_fetch_row($mysql_leftjoin_query)){
  echo "  <tr>
            <td>$data[0]</td>
            <td>$data[1]</td>
            <td>$data[2]</td>
          </tr>
        </table>"; } 
?>

only the first entry of the left joined table gets into table - $data[3] contains only the first value of the second table that matches, but i want all of the entries that match in $data[3]. Please have a look at the fiddle, I'm struggling to put this into words but i think it's a very simple set up. I think it has something to do with php's mysql_fetch_row but I can't put my finger on it.

只有左连接表的第一个条目进入表 - $ data [3]只包含匹配的第二个表的第一个值,但我想要所有在$ data [3]中匹配的条目。请看一下这个小提琴,我正在努力把它说成文字,但我认为这是一个非常简单的设置。我认为它与php的mysql_fetch_row有关,但我不能指责它。

For completeness, here is my MySQL query (simplified):

为了完整性,这是我的MySQL查询(简化):

$mysql_leftjoin_query = mysql_query("SELECT MASTER.animal, MASTER.species, BI.properties

 FROM        `XYZ_MASTER`  MASTER
    LEFT JOIN  `XYZ_BOARD_INFO` BI

        ON (MASTER.animal = BI.animal)");

Now, BI.properties should count all the entries of BI where MASTER.animal and BI.animal match and not just the first single entry.

现在,BI.properties应该计算MAS的所有条目,其中MASTER.animal和BI.animal匹配,而不仅仅是第一个单项。

1 个解决方案

#1

You are maybe looking for the group_concat function:

您可能正在寻找group_concat函数:

SELECT     MASTER.animal, MASTER.species, 
           group_concat(BI.properties separator ', ') as Properties
FROM       MASTER
LEFT JOIN  BOARD_INFO BI
       ON (MASTER.animal = BI.animal)
GROUP BY   MASTER.animal, MASTER.species

See this fiddle.

看到这个小提琴。

Output of the SQL is:

SQL的输出是:

+--------+---------+-------------------------------+
| animal | species | properties                    |
+--------+---------+-------------------------------+
| dog    | mammal  | has ears, has a tail, has fir |
| cat    | mammal  | meows, hunts birds            |
| turtle | reptile | (null)                        | 
+--------+---------+-------------------------------+

Your PHP can stay like it is.

你的PHP可以保持原样。

#1