3

I have two tables in MySQL. One with Orders. One with Buyers. The Buyers table is the tricky one. I want to combine multiple rows from the Buyers table into one Row in my output. Here is what the tables look like what I'm trying to achieve... Is this even possible?

ORDERS Table

ID      PRDCT_ID    TITLE       ORDER_TYPE
219     2           Product2    Shop_Order
220     2           Product2    Shop_Order
221     1           Product1    WH_Order

BUYER Table:

ID  ORDER_ID    BUYER_KEY   BUYER_VALUE
43  219         first_name  Rebecca
44  219         last_name   DeVore
45  219         email       rebecca.devore@domain.com
46  220         first_name  Jesse
47  220         last_name   Lawrence
48  220         email       jesse@domain.com
49  221         first_name  Gary
50  221         last_name   Darrel
51  221         email       garydary@domain.com

Here's what I want the desired output to be...

ORDERS.ID   ORDERS.TITLE    ORDERS.ORDER_TYPE   "First Name"            "Last Name"             "Email"
219         Product2        Shop_Order          Rebecca                 DeVore                  rebecca.devore@domain.com
220         Product2        Shop_Order          Jesse                   Lawrence                jessie@domain.com
221         Product1        WH_Order            Gary                    Darrel                  garyday@domain.com

I haven't even gotten close with the code yet. I would call myself a beginner in MySQL, but I know enough to make myself dangerous. But, just so you can laugh at my attempt.. here's where I'm at with the code; It works great to bring back just the first name lol:

SELECT * 
FROM BUYER a, 
    ORDERS b 
WHERE a.post_id=b.id
AND b.post_type= "Shop_Order"
AND a.meta_key="first_name"

Edit; This question was marked as a duplicate. My question varies a bit. The answer to the question that this was marked as a duplicate of is suggesting to create a new table in the database which I cannot do. I'm only limited to these two tables.

Josh Martin
  • 41
  • 5
  • 1
    I've updated my question to explain why this isn't a duplicate. The solution to the other question will not work in my instance. – Josh Martin Apr 17 '15 at 21:50
  • 1
    Seems like you need to pivot the data - [check out this duplicate question](http://stackoverflow.com/questions/15510657/pivot-table-using-mysql). I've created a sql fiddle with a few samples on how you can do this - http://sqlfiddle.com/#!9/2da10/3 – Taryn Apr 17 '15 at 22:06

0 Answers0