MySQL Select with Where on a View calculated column results in Illegal mix of collations

Woocommerce, creating a view that wraps HTML around post_status, so post_status is now calculated column (yes stupid wordpress is ignoring newlines in the pasted SQL snippet)

,concat(
case
when coalesce(mecha_note, '') <> ''
then '
' else '' end, case when post_status like '%cancel%' or post_status like '%refund%' then concat('', post_status, '') when post_status like '%completed%' then concat('', post_status, '
') else post_status end, case when coalesce(mecha_note, '') <> '' then concat('Fulfillment note:

', mecha_note, '
')
else '' end
) as post_status

PHP -> SQL filtering by status errors:

select * from vOrdersList
where post_status <> 'wc-completed'
and post_status <> 'wc-cancelled'
and post_status <> 'wc-refunded';

Error Code: 1267. Illegal mix of collations (utf8mb4_bin,NONE) and (utf8_general_ci,COERCIBLE) for operation 'like' 0.235 sec

What is the collation of the original posts table source column:

select COLLATION(post_status) as post_status, 
COLLATION(user()) as user_func,
COLLATION(substring_index(user(), '@', 1)) as substr_func
from wp1_posts;

post_status, user_func, substr_func
'utf8mb4_unicode_ci', 'utf8_general_ci', 'utf8_general_ci'

What about the calculated column in the view?

select COLLATION(post_status) as post_status, 
COLLATION(user()) as user_func,
COLLATION(substring_index(user(), '@', 1)) as substr_func
from vOrdersList;

post_status, user_func, substr_func
'utf8mb4_bin', 'utf8_general_ci', 'utf8_general_ci'

My solution: set collation for the calculated column in the view to match the source column, revised view for that column (see last line)
(yes stupid wordpress is ignoring newlines in the pasted SQL snippet) :

 ,concat(
case
when coalesce(mecha_note, '') <> ''
then '
' else '' end, case when post_status like '%cancel%' or post_status like '%refund%' then concat('', post_status, '') when post_status like '%completed%' then concat('', post_status, '
') else post_status end, case when coalesce(mecha_note, '') <> '' then concat('Fulfillment note:

', mecha_note, '
')
else '' end
) collate utf8mb4_unicode_ci as post_status

Woocommerce -> MySQL -> vVariationMetaData vProductVariation

20181220 Product variations (attribute = length only currently)

alter view vVariationMetaData
as
-- 20181220 LSR
-- Product Variations meta data
-- Note is selected metadata only (not quite all)
select po.post_parent, pm.post_id
,max(case when meta_key = 'attribute_length' then meta_value else null end) as pvm_attribute_length
,max(case when meta_key = '_sku' then meta_value else null end) as pvm_sku
,max(case when meta_key = '_weight' then meta_value else null end) as pvm_weight
,max(case when meta_key = '_length' then meta_value else null end) as pvm_length
,max(case when meta_key = '_width' then meta_value else null end) as pvm_width
,max(case when meta_key = '_height' then meta_value else null end) as pvm_height
,max(case when meta_key = '_manage_stock' then meta_value else null end) as pvm_manage_stock
,max(case when meta_key = '_regular_price' then meta_value else null end) as pvm_regular_price
,max(case when meta_key = '_price' then meta_value else null end) as pvm_price
,max(case when meta_key = '_variation_description' then meta_value else null end) as pvm_variation_description
from wp1_postmeta pm
join wp1_posts po on po.ID = pm.post_id
where po.post_type = 'product_variation'
group by po.post_parent, pm.post_id;


alter view vProductVariations
as
-- 20181220 LSR
-- Product meta data
select ID, post_title, pm_sku, pvm.*
from vProductMetaData pm
join vVariationMetaData pvm on pvm.post_parent = pm.id
order by pm.ID, pvm_attribute_length*0;

xx

MFM Purchases

Initial versions 20181117

Keep track of purchases

' Snapshot in case ever lost

CREATE TABLE `m4m_wpProducts`
	(`wp_post_id` INT
	,`wp_post_title` TEXT
	,`wp_sku` TEXT
	,
KEY `iPostID` (`wp_post_id`) USING BTREE
);

insert into m4m_wpProducts (`wp_post_id`, `wp_post_title`, `wp_sku`)
select ID, post_title, pm_sku from vProductMetaData;

Purchases

CREATE TABLE `m4m_purchases`
	(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,`wp_post_id` INT NOT NULL
	,`site` TEXT
	,`who` TEXT
	,`placed` DATETIME
	,`received` DATETIME
	,`qty_ordered` INT
	,`qty_received` INT
	,`qty_into_stock` INT
	,`issues` TEXT
	,`cost_total` DECIMAL(10,2)
	,`cost_unit_as_received` DECIMAL(10,4)
	,`cost_unit_as_into_stock` DECIMAL(10,4)
	,`cost_replacement` DECIMAL(10,2)
    ,KEY `iPostID` (`wp_post_id`) USING BTREE
);

xx

alter view vPurchasesSummary as
-- 20181118 LSR
-- Initial
-- 20181207 LSR
-- Added firstorders & orders
select wp_post_id,
    round( sum(cost_total) / sum(qty_received), 4) as cost_wavg,
    min(placed) as firstorder,
    count(*) as orders
from  m4m_purchases
where qty_received <> 0
group by wp_post_id;

alter view vPurchases
-- 20181117 LSR
-- Initial version for stock purchases CRUD
as
select 
	concat('<a href="productbuy.php?wp_post_id=',wp_post_id,'&id=',id,'">OL',wp_post_id,'/',ID, '</a>') as edit_href,
	id,
    wp_post_id,
    site,
    who,
    placed,
    received,
    qty_ordered,
    qty_received,
    qty_into_stock,
    issues,
    cost_total,
    cost_unit_as_received,
    cost_unit_as_into_stock,
    cost_replacement
from m4m_purchases pu;

xx
ProductListInitial

alter view vProductSimple
-- 20181117 LSR
-- Initial version for stock management entry point
-- 20181229 LSR
-- Added 'left' to join on vPurchasesSummary
as
select pm.ID as wp_post_id, pm_sku, post_title, pm_stock,
	concat('<a href="productbuy.php?wp_post_id=',pm.ID,'" >OL',pm.ID, '</a>') as productbuy_href,
	pm_m4m_cost,
    pm_m4m_ok,
    pm_m4m_pnp_cost,
    pm_m4m_purchase_note,
    pm_m4m_qty_bought,
    pm_m4m_qty_inv,
    pm_m4m_repl_cost,
    pm_m4m_shrinkage,
    pm_m4m_stocktake20180331,
    ps.cost_wavg
from vProductMetaData pm
join vProductType pt on pt.ID = pm.ID
left join vPurchasesSummary ps on pm.ID = ps.wp_post_id
where product_type = 'simple';

xx

Woocommerce – Editing Orders

Paypal orders are sorta locked

xx

— wp1_postmeta

update wp1_postmeta
set meta_value = ‘0.00’
where post_id = 3387
and meta_key = ‘_order_shipping’;

update wp1_postmeta
set meta_value = ‘0.00’
where post_id = 3387
and meta_key = ‘_order_shipping_tax’;

update wp1_postmeta
set meta_value = ‘10.36’
where post_id = 3387
and meta_key = ‘_order_total’;

— wp1_woocommerce_order_itemmeta

update wp1_woocommerce_order_itemmeta oim
join wp1_woocommerce_order_items oi on oi.order_item_id = oim.order_item_id
set meta_value = ‘0.00’
where oi.order_id = 3387
and oi.order_item_type = ‘shipping’
and meta_key = ‘cost’;

update wp1_woocommerce_order_itemmeta oim
join wp1_woocommerce_order_items oi on oi.order_item_id = oim.order_item_id
set meta_value = ‘ a:1:{i:1;s:6:”0.00″;}’
where oi.order_id = 3387
and oi.order_item_type = ‘shipping’
and meta_key = ‘taxes’;

xx

Woocommerce -> MySql -> vTaxonomyChained vTaxonomyPaths vProductCatsSummary

  • Product categories have parents…
  • Products can be in more than 1 category

xx

 create view vProductCatsSummary
 as
 select ID
	,count(*) as cat_cnt
    ,min(tt.term_taxonomy_id) as cat_min
    ,max(tt.term_taxonomy_id) as cat_max
    ,(	select group_concat( tex.name separator ' | ' )
		from wp1_term_relationships trx
		JOIN wp1_term_taxonomy ttx ON trx.term_taxonomy_id = ttx.term_taxonomy_id
		JOIN wp1_terms tex ON ttx.term_id = tex.term_id
        where trx.object_id = ID
        and ttx.taxonomy = 'product_cat') as cats
from wp1_posts po
JOIN wp1_term_relationships tr ON po.ID = tr.object_id
join wp1_term_taxonomy tt on tr.term_taxonomy_id = tt.term_taxonomy_id
where  po.post_type = 'product'
	aND tt.taxonomy = 'product_cat'
group by ID;

xx

alter view vTaxonomyPaths
 as
 select tt_id
	,concat(
		case when tt3_id is not null then concat(te3_name, ' -> ') else '' end
        ,case when tt2_id is not null then concat(te2_name, ' -> ') else '' end
        ,case when tt1_id is not null then concat(te1_name, ' -> ') else '' end
        ,te_name) as cats
	,concat(
		case when tt3_id is not null then concat(te3_slug, '/') else '' end
        ,case when tt2_id is not null then concat(te2_slug, '/') else '' end
        ,case when tt1_id is not null then concat(te1_slug, '/') else '' end
        ,te_slug) as path
 from vTaxonomyChainedUp
where tt_taxonomy = 'product_cat';

xx

 create view vTaxonomyChainedUp
 as
 -- 20180808 LSR
 -- product categories
 -- daisychained up +3 levels via parents
 select tt0.term_taxonomy_id as tt_id
	,tt0.term_id as te_id
    ,tt0.taxonomy as tt_taxonomy
    ,tt0.description as tt_desc
    ,te0.name as te_name
    ,te0.slug as te_slug
--
	,tt1.term_taxonomy_id as tt1_id
	,tt1.term_id as te1_id
    ,tt1.taxonomy as tt1_taxonomy
    ,tt1.description as tt1_desc
    ,te1.name as te1_name
    ,te1.slug as te1_slug
--
	,tt2.term_taxonomy_id as tt2_id
	,tt2.term_id as te2_id
    ,tt2.taxonomy as tt2_taxonomy
    ,tt2.description as tt2_desc
    ,te2.name as te2_name
    ,te2.slug as te2_slug
--
	,tt3.term_taxonomy_id as tt3_id
	,tt3.term_id as te3_id
    ,tt3.taxonomy as tt3_taxonomy
    ,tt3.description as tt3_desc
    ,te3.name as te3_name
    ,te3.slug as te3_slug
--
 from  wp1_term_taxonomy tt0
 JOIN wp1_terms te0 ON tt0.term_id = te0.term_id
--
 left join wp1_term_taxonomy tt1 on tt1.term_taxonomy_id = tt0.parent
 left JOIN wp1_terms te1 ON tt1.term_id = te1.term_id
--
 left join wp1_term_taxonomy tt2 on tt2.term_taxonomy_id = tt1.parent
 left JOIN wp1_terms te2 ON tt2.term_id = te2.term_id
--
 left join wp1_term_taxonomy tt3 on tt3.term_taxonomy_id = tt2.parent
 left JOIN wp1_terms te3 ON tt3.term_id = te3.term_id;

xx

Woocommerce -> MySql -> vOrderSummaryByMonth

xx

alter view vOrderSummaryByMonth
as
-- 20190217 LSR
-- Add refund into cancelled total
-- Exclude refund from incompleted
-- 20180829 LSR
-- Split by Trademe not trademe
-- 20180803 0907 LSR
-- Order counts & sums by month
-- 20181128 1226 LSR
-- Added cancel_oimnet
-- Updated incomp_oimnet to be all except completed / cancelled
-- Removed incomp_om_gross
select max(LAST_DAY(concat(year(post_date), '-', month(post_date), '-','1'))) as month
	,vOIMS.trademeflag
	-- counts
	,sum(case when post_status like '%cancel%'
				or post_status like '%refund%' then 1 else 0 end ) as fail
	,sum(case when post_status like '%completed' then 1 else 0 end) as complete
	,sum(case when post_status like '%hold%' then 1 else 0 end) as hold
	,sum(case when post_status like '%process%' then 1 else 0 end) as processing
	,sum(case when  post_status not like '%cancel%'
				and post_status not like '%refund%'
				and post_status not like '%completed'
				and post_status not like '%hold%'
				and post_status not like '%process%' then 1 else 0 end) as UNKNOWN
	-- $ from orderitemmeta
	,sum(case when post_status like '%completed' then
		  `sum_oim->line_item->_line_total` else 0 end) as 'comp_oimnet'
	,sum(case when post_status like '%completed' then
		  `sum_oim->shipping->cost` else 0 end) as 'comp_ship'
	,sum(case when post_status like '%completed' then
		  `sum_oim->fee->_line_total` else 0 end) as 'comp_fee'
	,sum(case when post_status like '%completed' then
		  `sum_oim->line_item->_line_tax` else 0 end) as 'comp_tax'
	,sum(case when post_status like '%completed' then
		  `sum_oim->tax->shipping_tax_amount` else 0 end) as 'comp_shiptax'
	,sum(case when post_status like '%completed' then
		  `sum_oim->fee->_line_tax` else 0 end) as 'comp_feetax'
	-- $ from order meta & tax from orderitemmeta
	,sum(case when post_status like '%completed' then
		  coalesce(order_total,0) else 0 end) as completed_om_gross
	,sum(case when post_status like '%completed' then
		  coalesce(order_total,0)
		- (coalesce(order_tax,0) + coalesce(order_shipping_tax,0)) else 0 end) as completed_om_netofgst        
	,sum(case when post_status not like '%completed'
				and post_status not like '%cancelled'
                and post_status not like '%refund%' then
		  `sum_oim->line_item->_line_total` else 0 end) as 'incomp_oimnet'
	,sum(case when post_status like '%cancel%'
				or post_status like '%refund%'  then
		  `sum_oim->line_item->_line_total` else 0 end) as 'cancel_oimnet'
from vOrderMetaData om
join vOrderItemMetaSUM vOIMS on vOIMS.order_id = om.ID
group by concat(year(post_date), '-', lpad(month(post_date),2,'0'))
    ,vOIMS.trademeflag;

xx

Woocommerce -> MySQL -> VOrdersList

xx

alter view vOrdersList as
-- 20180807 LSR
-- Added posthaste edit A link
-- 20180802 1019
select concat('<a href="invoice.php?order=',ID,'&copyinvoice=N" target="_blank">OL', ID, '</a>') as NewInv
,concat('<a href="invoiceold.php?order=',ID,'" target="_blank">OL', ID, '</a>') as OldInv
,post_date
,order_total
,payment_method_title as pay_method
,post_status
,concat('<a href="mailto:'
,coalesce(billing_email, 'xxx@xxx.xxx')
,'?Subject=MMMMM FFFF MMMM Order #'  -- Company name here
,ID
,'&body=Thank%20you%20very%20much%20for%20your%20order%20which%20is%20with%20Post%20Haste.'
,'%20%20An%20invoice%20is%20included%20in%20the%20package%0D%0A%0D%0A'
,coalesce(ph_httpurlencoded, 'xxxxx')
,'%0D%0A%0D%0A---%0D%0A%0D%0A'
,'We%20appreciate%20your%20business.'
,'%20If%20you%20like%20our%20products%20%26%20prices%20please%2C%20spread%20the%20word%0D%0A%0D%0A---'
,'%0D%0A%0D%0ARegards%20%26%20thanks%20again%20for%20shopping%20with%20us%0D%0A%0D%0A'
,'LLLLLLl' -- Sender first name here
,'%20'
,'RRRR' -- Sender last name here
,'%0D%0A%0D%0A'
,'MMMMM%20FFF%20MMMMM%20LLLLL' -- URL encoded company name here
,'%0D%0A'
,'www.xxxxx.co.nz' -- URL encoded www...nz domain name here
,'" target="_top">Send Mail</a>') as On_Ship
,ph_href
,concat('<a href="posthasteedit.php?order=',ID, '" target="_blank">', ID, '</a>') as ph_edit
,concat(billing_first_name, ' ', billing_last_name) as who
-- $$ summaries from order items meta
,soim.`sum_oim->line_item->_line_total` as oim_Net
,soim.`sum_oim->shipping->cost` as oim_Ship
,soim.`sum_oim->fee->_line_total` as oim_PPal
,soim.`sum_oim->line_item->_line_tax`
+ soim.`sum_oim->tax->shipping_tax_amount`
+ soim.`sum_oim->fee->_line_tax` as oim_GST 
,soim.`sum_oim->line_item->_line_total`
+ soim.`sum_oim->shipping->cost`
+ soim.`sum_oim->fee->_line_total`
+ `sum_oim->line_item->_line_tax`
+ soim.`sum_oim->tax->shipping_tax_amount`
+ soim.`sum_oim->fee->_line_tax` as oim_Total
-- and finally a sanity check to help find some of those
-- annoying GST rounding descrepancies
,(soim.`sum_oim->line_item->_line_total`
+ soim.`sum_oim->shipping->cost`
+ soim.`sum_oim->fee->_line_total`
+ `sum_oim->line_item->_line_tax`
+ soim.`sum_oim->tax->shipping_tax_amount`
+ soim.`sum_oim->fee->_line_tax`) - cast(order_total as DECIMAL(12,4)) as oimT_less_omT
from vOrderMetaData omd
join vOrderItemMetaSUM soim on soim.order_id = omd.ID
order by ID desc;

xx

Woocommerce -> MySql -> vOrderAddresses

xx

Todo: change from  br to p tags

alter view vOrderAddresses
as
-- 20180822 LSR
-- Added Post_excerpt first 20 chars(Customer comment added at checkout)
-- 20180807 LSR
-- Added maint <a> link
-- 20180805 LSR
-- Original
select  ID
    ,concat('<a href="addressedit.php?order=',ID,'" target="_blank">OL', ID, '</a>') as edit_shipping_href
	,case when concat(
			coalesce(billing_first_name, '')
			,coalesce(billing_last_name, '')
			,coalesce(billing_company, '')
			,coalesce(billing_address_1, '')
			,coalesce(billing_address_2, '')
			,coalesce(billing_city, '')
			,coalesce(billing_postcode, '')
			,coalesce(billing_state, '')
			,coalesce(billing_country), '')
		<> concat(
			coalesce(shipping_first_name, '')
			,coalesce(shipping_last_name, '')
			,coalesce(shipping_company, '')
            ,coalesce(shipping_address_1, '')
            ,coalesce(shipping_address_2, '')
            ,coalesce(shipping_city, '')
            ,coalesce(shipping_postcode, '')
            ,coalesce(shipping_state, '')
            ,coalesce(shipping_country, '')) then 1 else 0 end as hasshiptoaddress
	,concat(
		case when coalesce(billing_first_name, '') <> '' then concat(billing_first_name, ' ') else '' end
		,case when coalesce(billing_last_name, '') <> '' then concat(billing_last_name, '</br>') else '</br>' end
		,case when coalesce(billing_company, '') <> '' then concat(billing_company, '</br>') else '' end
		,case when coalesce(billing_address_1, '') <> '' then concat(billing_address_1, '</br>') else '' end
		,case when coalesce(billing_address_2, '') <> '' then concat(billing_address_2, '</br>') else '' end
		,case when coalesce(billing_city, '') <> '' then concat(billing_city, ' ') else '' end
		,case when coalesce(shipping_postcode, '') <> '' then concat(shipping_postcode, '</br>') else '</br>' end
        -- no state for now
		,case when (coalesce(billing_country, '') <> ''
			and billing_country not like 'nz'
			and billing_country not like '%zealand') then billing_country else '' end) as billingaddresshtml
	,concat(
		case when coalesce(shipping_first_name, '') <> '' then concat(shipping_first_name, ' ') else '' end
		,case when coalesce(shipping_last_name, '') <> '' then concat(shipping_last_name, '</br>') else '</br>' end
		,case when coalesce(shipping_company, '') <> '' then concat(shipping_company, '</br>') else '' end
		,case when coalesce(shipping_address_1, '') <> '' then concat(shipping_address_1, '</br>') else '' end
		,case when coalesce(shipping_address_2, '') <> '' then concat(shipping_address_2, '</br>') else '' end
		,case when coalesce(shipping_city, '') <> '' then concat(shipping_city, ' ') else '' end
		,case when coalesce(shipping_postcode, '') <> '' then concat(shipping_postcode, '</br>') else '</br>' end
        -- no state for now
		,case when (coalesce(shipping_country, '') <> ''
			and shipping_country not like 'nz'
			and shipping_country not like '%zealand') then shipping_country else '' end) as shippingaddresshtml
	,concat(left(post_excerpt, 20), case when char_length(post_excerpt) > 20 then '...' else '' end) as post_excerpt20
from vOrderMetaData;

xx

Woocommerce -> MySQL -> vProductMetaData

xx

alter view vProductMetaData
as
	/*
	Author: LSR 20180402
	Woocommerce products are stored as Posts
	Products have multiple attributes.
	- Each attribute has it's own record in table ..postmeta
	- so if a product has 5 attributes = at least 5 records in this table for that product
	- Custom attributes generated by custom product options/plugins
	  also generate additional records in this table
	This View flatterns those attributes 1 one record per product:
	- returns 1 record per post_type = 'product'
	- each attribute is in its own column

	Assumes integrity in that an attribute as identified by meta_key exists
	only once per parent Product
	*/
select po.ID, po.post_title
	,max(case when meta_key = '_area' then meta_value else null end) as pm_area
	,max(case when meta_key = '_backorders' then meta_value else null end) as pm_backorders
	,max(case when meta_key = '_crosssell_ids' then meta_value else null end) as pm_crosssell_ids
	,max(case when meta_key = '_default_attributes' then meta_value else null end) as pm_default_attributes
	,max(case when meta_key = '_downloadable' then meta_value else null end) as pm_downloadable
	,max(case when meta_key = '_edit_last' then meta_value else null end) as pm_edit_last
	,max(case when meta_key = '_edit_lock' then meta_value else null end) as pm_edit_lock
	,max(case when meta_key = '_featured' then meta_value else null end) as pm_featured
	,max(case when meta_key = '_height' then meta_value else null end) as pm_height
	,max(case when meta_key = '_length' then meta_value else null end) as pm_length
	,max(case when meta_key = '_m4m_cost' then meta_value else null end) as pm_m4m_cost
	,max(case when meta_key = '_m4m_ok' then meta_value else null end) as pm_m4m_ok
	,max(case when meta_key = '_m4m_pnp_cost' then meta_value else null end) as pm_m4m_pnp_cost
	,max(case when meta_key = '_m4m_purchase_note' then meta_value else null end) as pm_m4m_purchase_note
	,max(case when meta_key = '_m4m_qty_bought' then meta_value else null end) as pm_m4m_qty_bought
	,max(case when meta_key = '_m4m_qty_inv' then meta_value else null end) as pm_m4m_qty_inv
	,max(case when meta_key = '_m4m_repl_cost' then meta_value else null end) as pm_m4m_repl_cost
	,max(case when meta_key = '_m4m_shrinkage' then meta_value else null end) as pm_m4m_shrinkage
	,max(case when meta_key = '_m4m_stocktake20180331' then meta_value else null end) as pm_m4m_stocktake20180331
	,max(case when meta_key = '_manage_stock' then meta_value else null end) as pm_manage_stock
	,max(case when meta_key = '_max_price_variation_id' then meta_value else null end) as pm_max_price_variation_id
	,max(case when meta_key = '_max_regular_price_variation_id' then meta_value else null end) as pm_max_regular_price_variation_id
	,max(case when meta_key = '_max_sale_price_variation_id' then meta_value else null end) as pm_max_sale_price_variation_id
	,max(case when meta_key = '_max_variation_price' then meta_value else null end) as pm_max_variation_price
	,max(case when meta_key = '_max_variation_regular_price' then meta_value else null end) as pm_max_variation_regular_price
	,max(case when meta_key = '_max_variation_sale_price' then meta_value else null end) as pm_max_variation_sale_price
	,max(case when meta_key = '_min_price_variation_id' then meta_value else null end) as pm_min_price_variation_id
	,max(case when meta_key = '_min_regular_price_variation_id' then meta_value else null end) as pm_min_regular_price_variation_id
	,max(case when meta_key = '_min_sale_price_variation_id' then meta_value else null end) as pm_min_sale_price_variation_id
	,max(case when meta_key = '_min_variation_price' then meta_value else null end) as pm_min_variation_price
	,max(case when meta_key = '_min_variation_regular_price' then meta_value else null end) as pm_min_variation_regular_price
	,max(case when meta_key = '_min_variation_sale_price' then meta_value else null end) as pm_min_variation_sale_price
	,max(case when meta_key = '_price' then meta_value else null end) as pm_price
	,max(case when meta_key = '_product_addons' then meta_value else null end) as pm_product_addons
	,max(case when meta_key = '_product_addons_exclude_global' then meta_value else null end) as pm_product_addons_exclude_global
	,max(case when meta_key = '_product_attributes' then meta_value else null end) as pm_product_attributes
	,max(case when meta_key = '_product_image_gallery' then meta_value else null end) as pm_product_image_gallery
	,max(case when meta_key = '_product_version' then meta_value else null end) as pm_product_version
	,max(case when meta_key = '_psad_featured_order' then meta_value else null end) as pm_psad_featured_order
	,max(case when meta_key = '_psad_onsale_order' then meta_value else null end) as pm_psad_onsale_order
	,max(case when meta_key = '_purchase_note' then meta_value else null end) as pm_purchase_note
	,max(case when meta_key = '_regular_price' then meta_value else null end) as pm_regular_price
	,max(case when meta_key = '_sale_price' then meta_value else null end) as pm_sale_price
	,max(case when meta_key = '_sale_price_dates_from' then meta_value else null end) as pm_sale_price_dates_from
	,max(case when meta_key = '_sale_price_dates_to' then meta_value else null end) as pm_sale_price_dates_to
	,max(case when meta_key = '_sku' then meta_value else null end) as pm_sku
	,max(case when meta_key = '_sold_individually' then meta_value else null end) as pm_sold_individually
	,max(case when meta_key = '_stock' then meta_value else null end) as pm_stock
	,max(case when meta_key = '_stock_status' then meta_value else null end) as pm_stock_status
	,max(case when meta_key = '_tax_class' then meta_value else null end) as pm_tax_class
	,max(case when meta_key = '_tax_status' then meta_value else null end) as pm_tax_status
	,max(case when meta_key = '_thumbnail_id' then meta_value else null end) as pm_thumbnail_id
	,max(case when meta_key = '_upsell_ids' then meta_value else null end) as pm_upsell_ids
	,max(case when meta_key = '_virtual' then meta_value else null end) as pm_virtual
	,max(case when meta_key = '_visibility' then meta_value else null end) as pm_visibility
	,max(case when meta_key = '_volume' then meta_value else null end) as pm_volume
	,max(case when meta_key = '_wc_average_rating' then meta_value else null end) as pm_wc_average_rating
	,max(case when meta_key = '_wc_measurement_price_calculator_min_price' then meta_value else null end) as pm_wc_measurement_price_calculator_min_price
	,max(case when meta_key = '_wc_price_calculator' then meta_value else null end) as pm_wc_price_calculator
	,max(case when meta_key = '_wc_price_calculator_pricing_rules' then meta_value else null end) as pm_wc_price_calculator_pricing_rules
	,max(case when meta_key = '_wc_rating_count' then meta_value else null end) as pm_wc_rating_count
	,max(case when meta_key = '_weight' then meta_value else null end) as pm_weight
	,max(case when meta_key = '_width' then meta_value else null end) as pm_width
	,max(case when meta_key = '_woocommerce_add_fees_product' then meta_value else null end) as pm_woocommerce_add_fees_product
	,max(case when meta_key = '_wp_old_slug' then meta_value else null end) as pm_wp_old_slug
	,max(case when meta_key = 'total_sales' then meta_value else null end) as pmtotal_sales
from wp1_posts po
left join wp1_postmeta pm
	on pm.post_id = po.ID
where post_type = 'product'
group by po.ID

xx