SQL JOIN -需要返回一个产品id，但它的所有变化(例如大小，颜色) - SQL JOIN - Need to return one product id but all variations of it (e.g. Size, colour)

SQL JOIN - Need to return one product id but all variations of it (e.g. Size, colour)

我要加入几个表来显示股票表中的产品详细信息。因此，由于多种颜色和尺寸，一个产品ID可以在库存表中有多个条目。

表1:tblStock

stockID
productID
sizeID
colourID
qty

表2:tblColour

   colourID 
   colourName
   colourHEX

表3:tblSize

   sizeID
   sizeName

我查询:

    SELECT p.productID, c.colourName, c.colourHEX, sz.sizeName, s.qty
    FROM tblProducts p
    INNER JOIN tblStock s ON p.productID = s.productID
    INNER JOIN tblColour c ON s.colourID = c.colourID
    INNER JOIN tblSize sz ON s.sizeID = sz.sizeID
    WHERE p.productID = '$id'

   productID   colourName   colourHEX   sizeName       qty
   4           Burgundy     #621b40     Small          10
   4           Burgundy     #621b40     Medium         15
   4           Burgundy     #621b40     Large          20
   4           Pink         #ba0046     Large          20

是否有任何方法，我只能返回产品ID一次，但保留所有的变化?

如果没有，我将如何将这个产品返回到页面，因为我不希望它出现4次。

可以使用group_concat函数

SELECT p.productID, group_concat(distinct c.colourName), group_concat(distinct c.colourHEX), group_concat(distinct sz.sizeName), sum(s.qty)
FROM tblProducts p
INNER JOIN tblStock s ON p.productID = s.productID
INNER JOIN tblColour c ON s.colourID = c.colourID
INNER JOIN tblSize sz ON s.sizeID = sz.sizeID
WHERE p.productID = '$id'
group by p.productID ;// group by required only if you product id clause is removed

我不习惯PHP，但你的问题很简单。

你所需要的只是像这样在循环中添加一个单独的变量:

sql="SELECT p.productID, c.colourName, c.colourHEX, sz.sizeName, s.qty"
sql=sql+"FROM tblProducts p"
sql=sql+"INNER JOIN tblStock s ON p.productID = s.productID"
sql=sql+"INNER JOIN tblColour c ON s.colourID = c.colourID"
sql=sql+"INNER JOIN tblSize sz ON s.sizeID = sz.sizeID"
sql=sql+"WHERE p.productID = '$id'"
Set rs = RecordSetForStoringSQLQueryResult -- Replace with appropriate code in PHP
rs.Open (connectionName, sql)
$prevProductID = Null; -- Or some other number
while not rs.EndOfFile
    $currentId = rs("productID")
    if ($currentId != $lastId) {
         -- New ID found, display it.
         echo($currentId); 
         $prevProductID = $currentId
    } else { 
         -- Code for logic when the current product ID is same as previous product ID
         -- You will not assign anything to $prevProductID in this section.
    }
    rs.MoveToNextRecord
end while

请考虑我不是一个PHP的家伙，但我做了类似的事情在经典ASP。上面的代码只是为了说明目的，不能直接使用。你需要根据你的计划来调整它