Please bear with me. I have already searched through the posts recommended before I started writing this, but nothing seems to be similar to my situation, so here goes.
The setup
Sample Data
Order Table (in this example, 3 orders were placed for product #234)
12, 234, 1
13, 234, 2
14, 234, 3
Customer Table
1, Sarah, [email protected], 111-2345
2, Lyn, [email protected], 112-3434
3, John, [email protected], 123-4444
Order Status Table
12, 2021-02-01, 1, paid
13, 2021-02-03, 2, awaiting payment
14, 2021-02-08, 3, pending
Metadata Table
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
Expected Output (some values shortened to save space)
custID | Name | email | orderID | orderDate | payment | status | city | state
============================================================================================
1 | Sarah | [email protected] | 12 | 2021-02-01 | cash | paid | SF | LUN
2 | Lyn | [email protected] | 13 | 2021-02-03 | bank | waiting| QC | NCR
3 | John | [email protected] | 14 | 2021-02-08 | credit | pending| QC | NCR
So far, I'm able to fetch the data corresponding to the "payment" column by way of INNER JOINs, and basically I'm able to fetch the necessary data from the Metadata table by way of a subquery. I'm having difficulty in "JOINING" the first JOIN and the subquery result set.
Here's my code for the JOIN:
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')
Resultset:
custID | custName | email | orderID | orderDate | payment_method | status
1 | Sarah | [email protected] | 12 | 2021-02-01 | cash | paid
2 | Lyn | [email protected] | 13 | 2021-02-03 | deposit | awaiting payment
3 | John | [email protected] | 14 | 2021-02-08 | credit card | pending
And here's my code for the subquery:
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'
)
The ID is hardcoded in this instance so I could test the code. The resultset is:
id | key | value
===================================
12 | city | SF
12 | state | LUN
12 | payment_method | cash
How do I merge the two tables? Your help is highly appreciated. TIA!
You need a reference to metadata's tables for each metadata you need and for this you can use table's names alias
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'
and as suggested by @strawberry you should not use reserved word for table name ("order") .. if you really need then you should backtics (and alias)