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!