MySQL Unions – getting 2 values from 1 column

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

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'

Result:

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!