INNER JOIN and multi-row data in SQL, wp_postmeta table-CodePudding

I am trying to make a report with different data from my WordPress tables. I would like to know which referencing has made how many views and display the phone number there. I already have all the data in my table, but I'm not doing it right for my query.

Here are my (summary) tables and contents.

Table wp_posts

id	post_title
100	First Post
200	Second Post

Table wp_postmeta

meta_id	post_id	meta_key	meta_value
1	100	postviews_total	3493
2	100	listing_tel	04444444
3	200	postviews_total	2525
4	200	listing_tel	0555555

Desired outcome

Listing Title	Field	Value	Phone
First Post	postviews_total	3493	04444444
Second Post	postviews_total	2525	0555555

What can I do? I tried with this query to get my return, but I don't see how I can get a row added and my SQL query to understand that it's the same two values.

sql

SELECT wp_posts.post_title AS 'Listing Title',
wp_postmeta.meta_key AS 'Field', 
wp_postmeta.meta_value as 'Value', 
wp_640837_postmeta.meta_value as 'Phone' 
FROM wp_640837_posts 
JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id 
WHERE wp_postmeta.meta_key = 'post_views_count'

CodePudding user response：

try this. you need to join wp_postmeta table twice.

SELECT wp_posts.post_title AS 'Listing Title', postview.meta_key AS 'Field', postview.meta_value AS 'Value', tel.meta_value AS 'Phone'
FROM wp_640837_posts
    JOIN wp_postmeta AS postview ON wp_640837_posts.ID = postview.post_id AND postview.meta_key = 'postviews_total'
    JOIN wp_postmeta AS tel ON wp_640837_posts.ID = tel.post_id AND tel.meta_key = 'listing_tel'