CODEWITHSUNDEEP
jquerymysql
Sarah
Sarah

Reputation: 15

How to inner join rows from one column?

I am having a slow brain day...
I have a table like this
wp_postmeta

 post_id       meta_key       meta_value
      1         magTitle        Title1
      2         magTitle        Title2
      1         magTag          Tag1
      2         magTag          Tag1
      3         magTitle        Title3
      3         magTag          Tag2


I'd like to show this column like this:

 post_id       magTitle        magTag
      1         Title1          Tag1
      2         Title2          Tag1
      3         Title3          Tag2


I've tested with this query

SELECT `meta_value` FROM `wp_postmeta` WHERE `meta_key` = 'magTitle' OR `meta_key` = 'magTag'
INNER JOIN meta_key.magTitle ON meta_key.magTag
ORDER BY post_id ASC

but I got error;

Any track is appreciated.

Upvotes: 1

Views: 182

Answers (1)

Henry He
Henry He

Reputation: 1640

You can't really join columns, you join tables on column and your Join has to be in your where clause. given your table layout you'd probably want to join on the post_Id and the constrain the left and right sides by the meta_key. Something like.

SELECT 
    wppm.`meta_value` as magTitle, 
    wppm2.`meta_value` as magTag 
FROM 
    `wp_postmeta` as `wppm` 
INNER JOIN 
    `wp_postmeta` as `wppm2` 
    ON wppm.post_id = wppm2.post_id 
    AND wppm.meta_key = 'magTitle' 
    AND wppm2.meta_key = 'magTag' 
ORDER BY 
    wppm.post_id ASC

Upvotes: 1

Related Questions