CODEWITHSUNDEEP
mysqlwordpress
dingo_d
dingo_d

Reputation: 11680

Select specific values from the same column and display in different columns

I am trying to display two values from the same table in a sql query. The table holds 2 keys which have these values.

Currently, I made this

SELECT p.post_title, t.name, pm.meta_value AS address, pm.meta_value AS id
FROM `wp_posts` p
  LEFT JOIN wp_term_relationships trr ON p.ID = trr.object_id
  LEFT JOIN wp_term_taxonomy tax ON trr.term_taxonomy_id = tax.term_taxonomy_id
  LEFT JOIN wp_terms t ON tax.term_taxonomy_id = t.term_id
  LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE
  p.post_type = 'network-type' AND t.slug = 'atm' AND
  pm.meta_key IN ('network_address', 'network_branch_atm_id')

But this shows

| post_title | name | address   | id        |
---------------------------------------------
| Post name  | bla  | 12        | 12        |
| Post name  | bla  | address 1 | address 1 |

The postmeta table has values like this

| meta_id | post_id | meta_key              | meta_value |
----------------------------------------------------------
| 1212    | 323     | network_address       | address 1  |
| 1212    | 323     | network_branch_atm_id | 12         |

I tried using CASE but got errors (user not allowed to execute).

What should I change to get

| post_title | name | address   | id        |
---------------------------------------------
| Post name  | bla  | address 1 | 12        |

Upvotes: 1

Views: 29

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522471

Aggregate on the post title name, and then pivot out the address and id. The difficulty you are having (and which I also find with the wm_postmeta table) is that information is stored as keys and values. It can require some massaging to extract data the way you want it from the Wordpress schema.

SELECT
    p.post_title,
    t.name,
    MAX(CASE WHEN pm.meta_key = 'network_address'       THEN pm.meta_value END) AS address,
    MAX(CASE WHEN pm.meta_key = 'network_branch_atm_id' THEN pm.meta_value END) AS id
FROM wp_posts p
LEFT JOIN wp_term_relationships trr
    ON p.ID = trr.object_id
LEFT JOIN wp_term_taxonomy tax
    ON trr.term_taxonomy_id = tax.term_taxonomy_id
LEFT JOIN wp_terms t
    ON tax.term_taxonomy_id = t.term_id
LEFT JOIN wp_postmeta pm
    ON p.ID = pm.post_id
WHERE
    p.post_type = 'network-type' AND
    t.slug = 'atm' AND
    pm.meta_key IN ('network_address', 'network_branch_atm_id')
GROUP BY
    p.post_title,
    t.name;

Upvotes: 1

Related Questions