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;
1条答案
按热度按时间8wigbo561#
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.