Mysql Pivot/crosstab Query using JOIN query

Question

I have 3 tables, and I want to join all 3 table using pivot/crosstab. Let me give you structure of table.

Table name : wp_frm_item

id        post_id     user_id      name  

121       19241          3          balaji

120       19239          3          bajaj 

119       19235          3          pressur Cooker

Table name : wp_postmeta

  post_id        meta_key       meta_value 

    19241          Price:         590

    19241          Shipping:       20

    19241          Size:           250X20

    19239          price          8412

    19239          Shipping:       20

Table Name : wp_frm_item_meta

    item_id        field_id       meta_value 

    121             96            happy

    121             388            a:3

    121             237           sell 

    120             96            no I'm sad

    120             388           a:4

    120             237           swap

And I want Output exactly like below:

post_id   item_id  user_id  name   price   shipping  size     96       388   237  


19241     121      3       balaji   590     20       250X20  happy   a:3    sell

Please help to execute query for this and thank you so much in advanced to read my post .

There are many answers showing how to pivot MySQL tables. What is different about your question so you can't use one of those answers? — Barmar, Dec 20 '13 at 09:36
yes dear I know there are number of answers available here but I'm still stuck at join with other table. If you have link available for same question please paste here, it might help to me — Mitul Shah, Dec 20 '13 at 09:47
Here's a similar one I answered a couple of days ago: http://stackoverflow.com/questions/20623866/join-tables-listing-rows-as-columns-joined-to-another-table/20626119#20626119 — Barmar, Dec 20 '13 at 09:56
Thank you very very much. Your post gives me a lot help to build query and even my problem is also solve.Thank you so much — Mitul Shah, Dec 20 '13 at 11:49

score 0 · Accepted Answer · edited Dec 20 '13 at 12:00

0

Thanks for help Barmar.

Solution is here.

SELECT i.post_id,
       i.name post_name,
       i.user_id,
       MAX(IF(p.meta_key = "Shipping:",p.meta_value, NULL)) shipping,
       MAX(IF(p.meta_key = "Price:", p.meta_value, NULL)) price,
       MAX(IF(p.meta_key = "Size:", p.meta_value, NULL)) size,
       MAX(IF(p.meta_key = "_wp_attached_file", p.meta_value, NULL)) wp_attached_file
FROM wp_frm_items i
JOIN wp_postmeta p ON i.post_id = p.post_id 
where i.post_id  = 19241 
GROUP BY i.post_id

Demo

edited Dec 20 '13 at 12:00

Barmar

741,623
53
500
612

answered Dec 20 '13 at 11:52

Mitul Shah

1,556
1
12
34

Note that CASE conforms to the SQL standard – Strawberry Dec 22 '13 at 13:54

Mysql Pivot/crosstab Query using JOIN query

1 Answers1