Tying user ids in a table to meaningful values in another table in SQL Server

Question

I have the following table, let's call it People:

id     | customer | shipper | buyer |
1      | 1        | 2       | 3     |
2      | 4        | 5       | 6     |
3      | 7        | 8       | 9     |

And this ties in to a User_ID table:

id     | Name  |
1      | Stan  |
2      | Brian |
3      | Amy   |

What's the best practice for returning the table such as People only instead of id's replacing that with actual values? I realize I could do this with subqueries... but I think there's a better way.

Answer 1

Use joins - here's a good link explaining them visually:

   SELECT c.name AS customer,
          s.name AS shipper,
          b.name AS buyer
     FROM PEOPLE p
LEFT JOIN USER c ON c.id = p.customer
LEFT JOIN USER s ON s.id = p.shipper
LEFT JOIN USER b ON b.id = p.buyer