从两个表中添加具有相同名称的数量

Question

我有一个表，它有一个包含15个字符的部件号，在这个字符串中包含了4个不同的特性。我正在对表进行查询，然后去掉要在其他表中使用的不同的数字字符串。我遇到的问题是，当有多个具有相同值的子字符串时。然后，我通过我的材料清单表，以获得所需的数量，以作出整体部分。但是当我想让它返回所有重复的和时，它是返回重复的。

这就是我要做的：

$result = mysql_query("SELECT * FROM $table WHERE `active` = '1'");
    while ($r = mysql_fetch_assoc($result)) {
        $bucket_back_num = substr($r['part_num'],0,7);
        $bucket_bottom_num = substr($r['part_num'],0,7);
        $hooks_part_num = substr($r['part_num'],7,11-7);
        $grapple_part_num = substr($r['part_num'],11,15-11);
        $bucket_back_num .= $grapple_part_num;

        $result2 = mysql_query("SELECT *,sum(qty) as qty from bucket_part_bom WHERE `parent_part_num` = '$bucket_back_num' GROUP BY parent_part_num");
        while ($r2 = mysql_fetch_assoc($result2)) {
            echo '
                <tr>
                    <td style="width: 15%;">'.$r2['part_num'].'</td>
                    <td style="width: 25%; text-align: left;">'.$r2['desc'].'</td>
                    <td style="width: 15%;">'.$r2['qty'].'</td>
                    <td style="width: 15%;">Unpainted</td>
                    <td style="width: 10%;">Diff</td>
                    <td style="width: 10%;">Build</td>
                    <td style="width: 10%;">N/A</td>
                </tr>
            ';
        }
    }

正在归还的是：

LM102BRBK   Bucket Back 102" Light Material Bare    1
LM1023XBK   Bucekt Back 102" Light Material, GF3X   1
LM1023XBK   Bucekt Back 102" Light Material, GF3X   1

Answer 1

将这两个查询组合成一个带有JOIN的查询

SELECT t.*, b.*, SUM(b.qty) AS qty
FROM $table AS t
JOIN bucket_part_bom AS b ON b.parent_part_num = SUBSTR(t.part_num, 1, 7)
GROUP BY b.parent_part_num