i have 2 table 1 table have 1 column with unique key and another table with multiple unique key column

Question

table 1

status_id	detail_id	region_id	cust_name
0000	1212	1112	jack
0101	2222	1113	jill

table 2

value_id	value
0000	request
0101	done
1212	pending
2222	reprocess
1112	north america
1113	europe

desired result

status	detail	region	cust_name
request	pending	north america	jack
done	reprocess	europe	jill

i tried with this query, is there any other methods ?

select 
b.value AS status,
c.value AS detail,
d.value AS region,
a.cust_name
from table1 a
left join table2 b ON a.status_id =b.value_id
left join table2 c ON a.detail_id = c.value_id
left join table2 d ON a.region_id = d.value_id;

Answer 1

Your query is appropriate for the given task. You should not use table aliases, though, that don't carry meaning. a, b, c don't tell us anything and it's easy to confuse these. Use mnemonic aliases instead, like s for statuses, d for details and r for regions.

But there is a problem with your data model. As you are using one table for different things, you cannot guarantee data integrity. While you can say that a status_id, a detail_id and a region_id must be found in the values table, the DBMS does not know which row is which. Thus it can happen that you store a row with status = north america, detail = europe and region = pending without the DBMS interfering. Change your database and create one table per entity to get this straight and have the DBMS help you write consistent data.