There are such tables, the data of which I need to combine on the basis of one parameter (subscriber_id and ID), where one and the second is equal to '2'

First table Second table

I make a request ..

SELECT wp_mymail_subscriber_fields.meta_value, wp_mymail_subscribers.email FROM wp_mymail_subscribers, wp_mymail_subscriber_fields, wp_mymail_subscriber_meta WHERE wp_mymail_subscriber_meta.subscriber_id = '2' AND wp_mymail_subscriber_fields.subscriber_id = '2' AND wp_mymail_subscribers.ID = '2' group by `meta_value` ORDER BY `meta_value` DESC

In return, I get

answer

But how to make meta_value, where meta_key = 'firstname' is output as fname, and meta_value, where meta_key = 'lastname' as lname?

  • Try to reformulate the question so that it is more abstract and useful to others, and not in the style of "write the code for me for this particular case." - Kromster

1 answer 1

for example:

SQL feeddle

MySQL 5.6 Schema Setup :

 create table s (id int, email text); create table m (sid int, mkey text, mvalue text); insert into s values (1, 'odin@domain.tld'), (2, 'dva@domain.tld'); insert into m values (1, 'firstname', 'вася'), (1, 'lastname', 'ΠΏΡƒΠΏΠΊΠΈΠ½'), (2, 'firstname', 'маня'), (2, 'lastname', 'Π·ΠΎΡ€ΡŒΠΊΠΈΠ½Π°');

Query 1 :

 select s.email, m1.mvalue as firstname, m2.mvalue as lastname from s left join m m1 on m1.sid = s.id and m1.mkey = 'firstname' left join m m2 on m2.sid = s.id and m2.mkey = 'lastname' where s.id = 2

Results :

 | email | mvalue | mvalue | |----------------|--------|----------| | dva@domain.tld | маня | Π·ΠΎΡ€ΡŒΠΊΠΈΠ½Π° |