Hey everyone! Here is how to retrieve multiple values from the same table. My project had to do with job positions, but I have made the example a little easier to follow:
Let’s say in the usermeta table, each person has a left pocket, and a right pocket. Still with me?
We want to find out if any user has an apple in either pocket. The apple(s) can be in a user’s left pocket, right pocket, or both!
Table Aliases:
ULP - Left Pocket
URP - Right Pocket
{code type=mysql}
SELECT ULP.user_id, ULP.meta_value AS left_pocket, URP.meta_value AS right_pocket
FROM wp_usermeta AS ULP
LEFT JOIN wp_usermeta AS URP ON URP.user_id=ULP.user_id AND URP.meta_key=”right_pocket”
WHERE ULP.meta_key=”left_pocket” AND ULP.meta_value=’apple’
UNION
SELECT URP.user_id, ULP.meta_value AS left_pocket, URP.meta_value AS right_pocket
FROM wp_usermeta AS URP
LEFT JOIN wp_usermeta AS ULP ON URP.user_id=ULP.user_id AND UMC.meta_key=”left_pocket”
WHERE URP.meta_key=”right_pocket” AND URP.meta_value=’apple’
{/code}
Result:
| user_id | left_pocket | right_pocket |
|---|---|---|
| 14 | apple | banana |
| 42 | apple | apple |
| 88 | nothing | apple |
This will return an array with ['user_id', 'left_pocket', 'right_pocket']. Now you have a filtered list of User’s that have apples in their pockets! This is essentially retrieving results for 1 item (user) with multiple entries that can contain a valid value ( a || b || a & b ). Hope this helps!