CODEWITHSUNDEEP
mysqlsqlwordpressjoin
Natacha Beaugeais
Natacha Beaugeais

Reputation: 1053

Joining 2 result sets to replace values in a column

I have 2 SQL result datasets that I need merged into one to look like:

Final Merged Table
-------------
320 Sydney
321 Brisbane
322 Melbourne

The SQL to achieve this is a little more complicated since every data column is saved in the DB as a separate record against the User ID (due to Wordpress and the way it stores 'meta_value' data)

First result set:

User Table (A)
-------------
320 119 
321 120 
322 121

Achieved with:

SELECT wp_frm_items.id, meta_value AS city  FROM `wp_frm_items` 

INNER JOIN `wp_frm_item_metas` on wp_frm_item_metas.item_id = wp_frm_items.id 

WHERE `form_id` = 9 AND  field_id = 219

Second result set:

Location Table (B)
-------------
119 Sydney
120 Brisbane
121 Melbourne

Achieved with:

SELECT wp_frm_items.id, meta_value FROM `wp_frm_items` 

INNER JOIN `wp_frm_item_metas` on wp_frm_item_metas.item_id = wp_frm_items.id 

WHERE `form_id` = 10

MySQL keeps telling me I have an error in my final join code at line 7 "INNER JOIN": (the above 2 SQL statements work fine)

SELECT * 

FROM (SELECT wp_frm_items.id, meta_value AS city  FROM `wp_frm_items` 
INNER JOIN `wp_frm_item_metas` on wp_frm_item_metas.item_id = wp_frm_items.id 
WHERE `form_id` = 9 AND  field_id = 219 ) AS A 

INNER JOIN

SELECT * 

FROM (SELECT wp_frm_items.id, meta_value FROM `wp_frm_items` 
INNER JOIN `wp_frm_item_metas` on wp_frm_item_metas.item_id = wp_frm_items.id 
WHERE `form_id` = 10) AS B 

ON A.city = B.id

Can anyone shed some light for me on how I can achieve this?

Upvotes: 0

Views: 96

Answers (1)

lc.
lc.

Reputation: 116498

You're very close. The short answer is you don't need the SELECT * FROM in the join clause, just specify the (derived) table itself:

SELECT * 

FROM (SELECT wp_frm_items.id, meta_value AS city  FROM `wp_frm_items` 
INNER JOIN `wp_frm_item_metas` on wp_frm_item_metas.item_id = wp_frm_items.id 
WHERE `form_id` = 9 AND  field_id = 219 ) AS A 

INNER JOIN
    (SELECT wp_frm_items.id, meta_value FROM `wp_frm_items` 
    INNER JOIN `wp_frm_item_metas` on wp_frm_item_metas.item_id = wp_frm_items.id 
    WHERE `form_id` = 10) AS B 

ON A.city = B.id

Upvotes: 1

Related Questions