MYSQL - Getting the value from a field in a lookup table and main table in the same query

Question

I have 2 table which I will describe below:

1) The main table which has an auto increment by U_ID and has a column named RU_ID which refers to the ID of a lookup table.

2) The Lookup table is auto incremented by RU_ID and has a column named ROLE which has a sting value.

Now I am needing a SELECT statement that show me all the values of the main table (SELECT * FROM...) and the ROLE value of the lookup table.

I have this so far: Note that the = 2 is static for this example only, this will be a variable which holds the RU_ID number. Also this example will be used in PHP - PDO, but the SQL query is generic really.

    SELECT * 
    FROM tbl_users as u 
    WHERE u.RU_ID = 2
    AND STATUS = 1 
    AND u.RU_ID IN 
    (SELECT ROLE 
     FROM tbl_role_users, tbl_users
     WHERE RU_ID = 2)";

Thanks for looking into this with me.

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

have you tried a JOIN?

SELECT t.*, t1.role
FROM tbl_users t
JOIN tbl_role_users t1 ON t.RU_ID = t1.RU_ID -- # --or t1.ID whichever is referenced
WHERE t1.RU_ID = 2
  AND t.status = 1;

if you want to join the table without filtering excess rows then just add LEFT before the JOIN

Note:

I would recommend you steer away from the pre ANSI way of joining tables and actually use the JOIN syntax as its easier to read in complicated queries and or you can have more functionality with the JOIN syntax.. and that way you dont need the IN() statement

MYSQL - Getting the value from a field in a lookup table and main table in the same query

1 Answers1

Note: