请求编写SQL查询语句：获取所有已订购产品的完整信息

Here's a query that handles both products with and without sizes, aligning perfectly with your table structure and business rules:

SELECT
    p.Name AS ProductName,
    p.inventoryid,
    p.hassizes,
    o.orderid,
    o.quantityordered,
    s.sizedesc AS SizeDescription
FROM OrdersInfo o
INNER JOIN ProductInfo p 
    ON o.inventoryid = p.inventoryid
LEFT JOIN SizeInfo s 
    ON o.inventoryid = s.inventoryid 
    AND o.sizeid = s.Sizeid;

Breakdown of the Query: #

• INNER JOIN with ProductInfo: We start from OrdersInfo (since we only care about products that have been ordered) and join to ProductInfo using inventoryid—this ensures we only pull in products that have at least one order.
• LEFT JOIN with SizeInfo: This accounts for products without sizes. For these items, there are no matching records in SizeInfo, so SizeDescription will return NULL—which matches your rule that OrdersInfo.sizeid is NULL for non-sized products. Joining on both inventoryid and sizeid prevents accidental matches between different products that might share the same sizeid value.
• Aliases: Using short aliases (p, o, s) keeps the query clean and easy to scan.

Key Observations: #

• For products that have sizes (hassizes = true), the query will return the exact sizedesc corresponding to the ordered size.
• For non-sized products, all size-related fields will show as NULL, which is consistent with your business logic.
• If you want to aggregate order data (like total quantity ordered per product), you can add a GROUP BY clause with an aggregate function like SUM(o.quantityordered).

内容的提问来源于stack exchange，提问作者LearningPhase