CODEWITHSUNDEEP
phpmysqlwordpressjoin
Cesar Henrique Damascena
Cesar Henrique Damascena

Reputation: 241

Join wp_postmeta table to itself to find all entries that relate to a particular meta key & value

I have a table wp_postmeta with these columns:

and I would like to retrieve only the following entries:

-----------------------------------------------------------------
| meta_id  | post_id  |    meta_key          |  meta_value      |
|----------|----------|----------------------|------------------|
|   1111   |   2222   | user_id              | 10               |
|----------|----------|----------------------|------------------|
|   1112   |   2222   | user_full_name       | Customer Name    |
|----------|----------|----------------------|------------------|
|   1113   |   2222   | renewal_order_total  | 00,00            |
|----------|----------|----------------------|------------------|
|   1114   |   2222   | order_bandeira       | Visa             |
|----------|----------|----------------------|------------------|
|   1115   |   2222   | order_token          | token            |
|----------|----------|----------------------|------------------|
|   1116   |   2222   | product_name         | Product X        |
-----------------------------------------------------------------

In my code, I have the user_id, I would like to get these values grouped by this user ID, as you saw above is a meta_key in this table I tried some queries but all of them were unsuccessful.

One of the queries I tried was based on answer of this question.

SELECT a.* FROM wp_postmeta a 
LEFT OUTER JOIN wp_postmeta b ON a.post_id = b.post_id AND b.meta_value = 10 
WHERE a.meta_value = 10 AND b.meta_key = 'user_id'

The results that I get with this query is only the row with the user_id

----------------------------------------------------------------|
| meta_id  | post_id  |    meta_key          |  meta_value      |
|----------|----------|----------------------|------------------|
|   1111   |   2222   | user_id              | 10               |
-----------------------------------------------------------------

Upvotes: 1

Views: 272

Answers (1)

FluffyKitten
FluffyKitten

Reputation: 14312

Your query is right in the way it joins the table to itself, but the problem is that you are then telling it only to return rows where a.meta_value = 10 instead of all the rows it found for that user_id.

Remove the a.meta_value condition from the WHERE like this, and it should work:

SELECT a.* FROM wp_postmeta a 
LEFT OUTER JOIN wp_postmeta b ON a.post_id = b.post_id AND b.meta_value = 10 
WHERE b.meta_key = 'user_id'

Upvotes: 1

Related Questions