johnnyd23 February 2016

SQL Query based on value of a different row

I'm trying to query data from multiple tables but cannot seem to achieve this type of JOIN. For example, I have one table with contains products, and a wp_usermeta table with various user meta data:

Products Table

Serial #     Product
---------------------
0001         A
0002         B
0003         C

wp_usermeta (simplified)

User ID     Key         Value
--------------------------------
1           Serials     0001
1           Company     Company A
2           Serials     0001
2           Company     Company B
3           Serials     0001
3           Company     Company C

I need to get the Company name based on the serial numbers they own, such as:

Serial #      Company
-------------------------
0001          Company A
0002          Company B
0003          Company C

I've been able to retrieve the User ID based on serial number with a simple join query, but I dont know where to go from there.

SELECT products.serial_number, wp_usermeta.user_id FROM products LEFT JOIN wp_usermeta ON products.serial_number = wp_usermeta.meta_value

Answers


mnv February 2016

You need 2 joins on wp_usermeta like this:

SELECT Products.Serial, w2.Value AS Company 
FROM Products
# Detect User Id by serial number
JOIN wp_usermeta w1 ON w1.Key = 'Serials' 
    AND w1.Value = Products.Serial
# And select company by detected User Id
JOIN wp_usermeta w2 ON w2.Key = 'Company'
    AND w2.User = w1.User


Gordon Linoff February 2016

I am not sure what the products table has to do with the results you need.

One method is a "simple" self-join:

SELECT p.value as serial_number, c.value as Company.user_id
FROM wp_usermeta p JOIN
     wp_usermeta c
     ON p.user = c.user AND
        p.key = 'Serials' AND
        c.key = 'Company';

Post Status

Asked in February 2016
Viewed 1,739 times
Voted 14
Answered 2 times

Search




Leave an answer