php - 按产品过滤订单

Question

我是 SQL 开发的新手。我不擅长 SQL 连接 :( 。

请帮我编写一个 SQL 查询，以从产品“家用可移植激光脱毛器”的下表中提取以下文件。

所需文件:

orders.id, orders.order_price, orders.purchase_date, customers.email, product_orders.qty, products.name

表 1:订单

id  customer_id order_price purchase_date
1   1                   0.20    12/6/2011
2   2                   0.20    12/6/2011
3   1                   0.20    12/6/2011
4   1                   0.20    12/6/2011
5   1                   0.20    12/7/2011
6   3                   199.00  12/7/2011
7   4                   199.00  12/7/2011
8   5                   199.00  12/7/2011
9   6                   199.00  12/7/2011
10  7                   199.00  12/7/2011

表 2:客户

id  email                   name
1   aa@dealboard.com.au aa
2   bb@dealboard.com.au bb
3   cc@live.com.au          cc
4   dd@acgglobal.com    dd
5   ee.heinrich@det.nsw.edu.au  ee
6   ff@optusnet.com.au  ff
7   ssy@hotmail.com         ss

表 3:产品

id  name
1   A Home Portable Laser Hair Remover
2   Ipad
3   android
4   Asus
5   s
6   10 inch Android
7   A Fabric Steamer Cleaner
8   A Magnetic Fly Screen Door
9   pillopw
10  LCD

表 4:product_orders

       id orders_id product_id qty
        1   1      1         1
        2   2      1         1
        3   3      1         1
        4   4      1         1
        5   5      2         1
        6   6      1         1
        7   7      1         1
        8   8      1         1
        9   9      1         1
        10  10     1         1

我想提取产品 ID 1 的所有订单或按产品过滤订单

请帮帮我

Answer 1

听起来您想连接所有表:

select orders.id, orders.order_price, orders.purchase_date, customers.email,
    product_orders.qty, products.name from orders, customers, product_orders,
    products where
    orders.customer_id = customers.id and
    product_orders.order_id = orders.id and
    products.id = product_orders.product_id and
    products.id = 1