MySQL Query Syntax

Nathan Riley
Nathan Riley used Ask the Experts™
on
I have a query that pulls meta data currently like:

SELECT order_id, meta_key, meta_value
FROM wp_woocommerce_order_items i
INNER JOIN wp_woocommerce_order_itemmeta im ON i.order_item_id = im.order_item_id
where meta_key like '%Name - %'
or meta_key like '%Job Title - %'
or meta_key like '%Company - %'
or meta_key like '%Email - %'

Open in new window


The output is:
2668 Name - 1 Nathan Riley
2668 Email - 1 nathan@domain.com
2800 Name - 1 John Smith
2800 Email - 1 john@domain2.com
2800 Name - 2 Jane Doe
2800 Email - 2 jane@domain2.com

How can I modify so the output would be:
2668 Nathan Riley nathan@domain.com
2800 John Smith john@domain2.com
2800 Jane Doe jane@domain2.com

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David FavorFractional CTO
Distinguished Expert 2018

Commented:
Use the SUBSTR() function, which can easily trim off some fixed number of leading characters.

https://www.w3resource.com/mysql/string-functions/mysql-substr-function.php provides a good + simple example.
Nathan RileyFounder

Author

Commented:
I need them in the same line though, right now they are each their own line, not sure how to do that with substr().
create a view of this query name as meta;
then do a self join like this
select  * from meta a  ,(select order_id ,meta_key as a ,meta_value as b from meta) as m where a.order_id=m.order_id and a.meta_key!=m.a and meta_key like "%name%" or "%email%"

here i have used self join to get that specific name and email keys records
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
Replace you meta column with below

SELECT CONCAT ( SUBSTRING(meta_data, 1,4) , ' ',  SUBSTRING(meta_data, 14,200) )
FROM wp_woocommerce_order_items i
INNER JOIN wp_woocommerce_order_itemmeta im ON i.order_item_id = im.order_item_id
where meta_key like '%Name - %'
or meta_key like '%Job Title - %'
or meta_key like '%Company - %'
or meta_key like '%Email - %'
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
where meta_key like '%Name - %'

Try to AVOID using LIKE with wildcards at BOTH ENDS of a string, these are performance killers as that cannot make use of any indexing. There really is no need for that here either, at worst you only needed 'Name -%'

When joining tables you should be precise whenever you can be, and it seems you can be here. So I suggest the following:
SELECT
      order_id
    , coalesce(name1.meta_value,name2.meta_value)  name
    , coalesce(email1.meta_value,email2.meta_value) email
FROM wp_woocommerce_order_items i
LEFT JOIN wp_woocommerce_order_itemmeta name1  ON i.order_item_id = im.order_item_id
                                               and meta_key = 'Name - 1'
LEFT JOIN wp_woocommerce_order_itemmeta email1 ON i.order_item_id = im.order_item_id
                                               and meta_key = 'Email - 1'
LEFT JOIN wp_woocommerce_order_itemmeta name2  ON i.order_item_id = im.order_item_id
                                               and meta_key = 'Name - 2'
LEFT JOIN wp_woocommerce_order_itemmeta email2 ON i.order_item_id = im.order_item_id
                                               and meta_key = 'Email - 2'

Open in new window


Oh, PLEASE always (always always always) prefix EVERY column reference by their table alias or table name if an alias hasn't been established.

e.g. I had to assume that meta_key and meta_value come from  wp_woocommerce_order_itemmeta
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
Commented:
Hi!

Try these two selects. These will probably give you an idea to solve this. :)

SELECT order_id, GROUP_CONCAT(CONCAT_WS(' ',meta_key, meta_value))
FROM wp_woocommerce_order_items i
INNER JOIN wp_woocommerce_order_itemmeta im ON i.order_item_id = im.order_item_id
where meta_key like '%Name - %'
or meta_key like '%Job Title - %'
or meta_key like '%Company - %'
or meta_key like '%Email - %'
group by order_id

Open in new window


SELECT order_id, GROUP_CONCAT(meta_value order by meta_key)
FROM wp_woocommerce_order_items i
INNER JOIN wp_woocommerce_order_itemmeta im ON i.order_item_id = im.order_item_id
where meta_key like '%Name - %'
or meta_key like '%Job Title - %'
or meta_key like '%Company - %'
or meta_key like '%Email - %'
group by order_id

Open in new window


See further here
https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat
http://www.mysqltutorial.org/mysql-group_concat/
https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_concat-ws


Regards,
      Tomas Helgi

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial