I have a users table and have roles such as admin|manager|employee which are represented in reference table called reference.
user table - id|first_name|last_name|type|status
reference table - id|table|type|key|value
Now the reference table contains integer key that match values so user.type has 0-admin,1-manager,2-employee which looks something like this
table:user
type:type
key:0
value:admin
My problem is when I have to values in a table which need to access the reference table.
table:user
type:status
key:0
value:enabled
Question: How can I access two reference table values in one statement?
//STATEMENT
SELECT a.id,a.first_name,a.last_name,b.value as user_type,b.value as user_status
FROM user AS a
JOIN reference as b
ON 'user'=b.table AND 'type'=b.type AND a.type = b.value AND a.status = b.value
You can join to the reference table twice (or three times, or four times...). Just give it two different aliases:
SELECT a.id,a.first_name,a.last_name,b.value as user_type,b.value AS user_type, b2.value as user_status
FROM user AS a
JOIN reference AS b
ON 'user'=b.table AND 'type'=b.type AND a.type = b.value
JOIN reference AS b2
ON 'user'=b2.table AND 'status'=b2.type AND a.status = b2.value
Unless I'm mis-interpreting your requirements, I believe the above is what you are seeking.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments