MySQL Query Syntax

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!
LVL 12
Nathan RileyFounderAsked:
Who is Participating?
 
hemant sainiCommented:
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
0
 
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
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.
0
 
Nathan RileyFounderAuthor 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().
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Pawan KumarDatabase ExpertCommented:
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 - %'
0
 
PortletPaulfreelancerCommented:
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
0
 
Tomas Helgi JohannssonCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.