I'm attempting (and failing) to work out how to write a query for a WordPress table.

The table is named wp_postmeta and contains 3 columns that are relevant - post_id, meta_key and meta_value.

First step - to return all meta_value's where the meta_key contains "rating". That bit I can do.

However, for each post_id with a meta_key of "rating" there is a matching record with a meta_key of "product". How do I return the post_id, product meta_value and rating meta_value as one record?

Many thanks,
David.

You need to do a full-outer self-join, so that you can denormalize the results and retrieve either product or rating if the other doesn't exist.

SELECT
    IFNULL(r.post_id, p.post_id) AS post_id,
    r.meta_value AS rating,
    p.meta_value AS product
FROM wp_postmeta AS r
FULL OUTER JOIN wp_postmeta AS p
    ON r.post_id = p.post_id
    AND r.meta_key = "rating"
    AND p.meta_key = "product"