Tying user ids in a table to meaningful values in another table in SQL Server
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 t开发者_高级运维able:
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.
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
精彩评论