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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
PortletPaulEE Topic AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.