内部连接与子查询

Question

请容忍我。在我开始写这篇文章之前，我已经搜索过推荐的帖子，但是似乎没有什么与我的情况类似，所以我就这样做了。

安装程序

contact)

• Order_Status

• 订单表(orderID，productID， Table (custID，custName，email，contact)

1. Order_Status Table ) (orderID，orderDate，custID，status)
2. Metadata Table (id，key，value))

样本数据

订购表(在本例中，对产品#234下了3次订单)

12, 234, 1
13, 234, 2
14, 234, 3

客户表

1, Sarah, sarah@gmail.com, 111-2345
2, Lyn, lyn@yahoo.com, 112-3434
3, John, john@gmail.com, 123-4444

订单状态表

12, 2021-02-01, 1, paid
13, 2021-02-03, 2, awaiting payment
14, 2021-02-08, 3, pending

元数据表

12, payment_method, cash
12, city, SF
12, state, LUN
13, payment_method, deposit
13, city, QC
13, state, NCR
14, payment_method, cc
14, city, QC
14, state, NCR
15, screen, 1024x768

预期产出(一些值缩短以节省空间)

custID | Name  |      email      | orderID | orderDate  | payment | status | city | state
============================================================================================
1      | Sarah | sarah@gmail.com |    12   | 2021-02-01 | cash    | paid   | SF   | LUN
2      | Lyn   | lyn@yahoo.com   |    13   | 2021-02-03 | bank    | waiting| QC   | NCR
3      | John  | john@gmail.com  |    14   | 2021-02-08 | credit  | pending| QC   | NCR

到目前为止，我能够通过内部连接的方式获取与"payment“列对应的数据，并且基本上能够通过子查询从元数据表中获取必要的数据。我在“加入”第一个联接和子查询结果集方面有困难。

下面是我的连接代码：

SELECT  customer.custID,
        customer.custName,
        customer.email,
        order.orderID,
        order_status.orderDate,
        metadata.value AS 'payment_method',
        order_status.status        
FROM    ((( customer
INNER JOIN order 
      ON customer.customer_id = order.customer_id )
INNER JOIN order_status 
      ON order_status.order_id = order.order_id)
INNER JOIN metadata
      ON metadata.id = order.order_id 
      AND metadata.key = 'payment_method')

成果集：

custID | custName | email           | orderID | orderDate  | payment_method | status
1      | Sarah    | sarah@gmail.com | 12      | 2021-02-01 | cash           | paid
2      | Lyn      | lyn@yahoo.com   | 13      | 2021-02-03 | deposit        | awaiting payment
3      | John     | john@gmail.com  | 14      | 2021-02-08 | credit card    | pending

下面是子查询的代码：

SELECT id, key, value FROM metadata 
       WHERE id = 12
       AND key = ANY (
           SELECT key 
           FROM metadata 
           WHERE key = 'payment_method' 
             OR meta_key = 'city'
             OR meta_key = 'state' 
       ) 

ID在这个实例中是硬编码的，所以我可以测试代码。结果是：

  id | key            | value
===================================
  12 | city           | SF
  12 | state          | LUN
  12 | payment_method | cash

如何合并这两张表？你的帮助是非常感谢的。蒂娅！

Answer 1

您需要对所需的每个元数据引用元数据的表，为此可以使用表的名称别名

SELECT  customer.custID,
        customer.custName,
        customer.email,
        o.orderID,
        order_status.orderDate,
        m1.value AS 'payment_method',
        m2.value AS 'city',  
        m3.value AS 'state'     
        order_status.status        
FROM    customer
INNER JOIN `order` o  ON customer.customer_id = o.customer_id 
INNER JOIN order_status ON order_status.order_id = o.order_id
INNER JOIN metadata m1 ON m1.id = o.order_id 
      AND m1.key = 'payment_method'
INNER JOIN metadata m2 ON m2.id = o.order_id 
      AND m2.key = 'city'
INNER JOIN metadata m3 ON m3.id = o.order_id 
      AND m3.key = 'state'

正如@草莓建议的那样，您不应该为表名("order")使用保留词。如果你真的需要，那么你应该背背(和别名)