MySQL多条件查询需求：五表关联筛选特定购买记录的客户信息

No worries—multi-table joins can feel overwhelming at first, but let's map out exactly which tables we need to connect and why, then build the query step by step.

First, let's confirm the table relationships we'll use (aligned with typical retail database structures matching your description):

• LGCUSTOMER: Stores the customer details we need to output (first name, last name, address, etc.)
• LGINVOICE: Links customers to their orders, and includes the order date we need to filter on
• LGINVLINE: The junction table between invoices and products (since one invoice can contain multiple products)
• LGPRODUCT: Lets us filter for the "Top Coat" product category
• LGBRAND: Targets the "Foresters Best" brand using the given brand_id=23

Final MySQL Query #

SELECT DISTINCT
    c.cust_first_name AS `first name`,
    c.cust_last_name AS `last name`,
    c.cust_street AS street,
    c.cust_city AS city,
    c.cust_state AS state,
    c.cust_zip AS `zip code`
FROM
    LGCUSTOMER c
JOIN
    LGINVOICE i ON c.cust_id = i.cust_id
JOIN
    LGINVLINE il ON i.inv_num = il.inv_num
JOIN
    LGPRODUCT p ON il.prod_sku = p.prod_sku
JOIN
    LGBRAND b ON p.brand_id = b.brand_id
WHERE
    b.brand_id = 23 -- Matches "Foresters Best" brand
    AND p.prod_category = 'Top Coat'
    AND i.inv_date BETWEEN '2013-07-15' AND '2013-07-31'
ORDER BY
    c.cust_state,
    c.cust_last_name,
    c.cust_first_name;

Breakdown of Key Parts: #

• JOIN Clauses: We use inner joins to only retain records that have matches across all linked tables—this ensures we only get customers who actually placed orders for the specific product/brand we're targeting.
• DISTINCT: Guarantees that even if a customer bought the product multiple times, their personal details only appear once in the results.
• Filter Conditions:
  • Targets the correct brand via brand_id=23
  • Narrows results to "Top Coat" category products
  • Restricts orders to the July 15-31, 2013 date range
• ORDER BY: Sorts the final output first by state, then last name, then first name as requested.

If your actual table column names differ slightly (e.g., customer_id instead of cust_id), just adjust those to match your schema—this core structure will still work for your use case.

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