Re: Wordpress Metadata and Value

[Jay 147]

Hi Peter,
Here is what i have so far, now as a sql statement this works, however when i put it into a View I get an error "view's select contains a subquery in the from clause"
Maybe there is a better way to do this?

CREATE VIEW view_name AS
select n1.user_id, m1.moodle_user_id, n1.meta_value as nickname, f1.firstname, l1.lastname, b1.billingemail,
b2.billingaddress1, b3.billingaddress2, b4.billingcompany, b5.billingcity, b7.billingpostalcode from wp_usermeta as n1
left JOIN (select f1.user_id, f1.meta_value as firstname from wp_usermeta as f1 where f1.meta_key='first_name') f1
on n1.user_id = f1.user_id
left JOIN (select l1.user_id, l1.meta_value as lastname from wp_usermeta as l1 where l1.meta_key='last_name') l1
on n1.user_id = l1.user_id
left JOIN (select b1.user_id, b1.meta_value as billingemail from wp_usermeta as b1 where b1.meta_key='billing_email') b1
on n1.user_id = b1.user_id
left JOIN (select b2.user_id, b2.meta_value as billingaddress1 from wp_usermeta as b2 where b2.meta_key='billing_address_1') b2
on n1.user_id = b2.user_id
left JOIN (select b3.user_id, b3.meta_value as billingaddress2 from wp_usermeta as b3 where b3.meta_key='billing_address_2') b3
on n1.user_id = b3.user_id
left JOIN (select b4.user_id, b4.meta_value as billingcompany from wp_usermeta as b4 where b4.meta_key='billing_company') b4
on n1.user_id = b4.user_id
left JOIN (select b5.user_id, b5.meta_value as billingcity from wp_usermeta as b5 where b5.meta_key='billing_city') b5
on n1.user_id = b5.user_id
left JOIN (select b6.user_id, b6.meta_value as billingstate from wp_usermeta as b6 where b6.meta_key='billing_state') b6
on n1.user_id = b6.user_id
left JOIN (select b7.user_id, b7.meta_value as billingpostalcode from wp_usermeta as b7 where b7.meta_key='billing_postacode') b7
on n1.user_id = b7.user_id
left JOIN (select m1.user_id, m1.meta_value as moodle_user_id from wp_usermeta as m1 where m1.meta_key='moodle_user_id') m1
on n1.user_id = m1.user_id
where n1.meta_key='nickname';