Jordan Davis Jordan Davis - 7 months ago 12
SQL Question

selecting values from a reference table

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

Answer

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.