I'm trying to query data from multiple relational tables in one query. I have a receipt table which holds information for customer purchases by storing user_id and customer_id as a FK. I want to query the entire lists of receipts while getting the user_name and customer_name. Can/Should this be done in a single query?
Receipt Table
*--------------------------------------------------*
|receipt_id | user_id | customer_id | receipt_info |
| 1 | 1 | 1 | 'Some text' |
| 2 | 2 | 1 | 'Some text' |
| 3 | 2 | 1 | 'Some text' |
| 4 | 3 | 2 | 'Some text' |
| 5 | 3 | 3 | 'Some text' |
*--------------------------------------------------*
User Table
*-----------------------*
|user_id | user_name |
| 1 | Michael |
| 2 | Dwight |
| 3 | Jim |
| 4 | Andy |
| 5 | Stanley |
*-----------------------*
Customer Table
*---------------------------*
|customer_id| customer_name |
| 1 | Schofield |
| 2 | Julia |
| 3 | Dunmore High|
| 4 | Deckert |
| 5 | Prince Paper|
*---------------------------*
So I want my result set to be something like this:
Results Table
*------------------------------------------------------*
|receipt_id | user_name | customer_name | receipt_info |
| 1 | Michael | Schofield | 'Some text' |
| 2 | Dwight | Schofield | 'Some text' |
| 3 | Dwight | Schofield | 'Some text' |
| 4 | Jim | Julia | 'Some text' |
| 5 | Jim | Dunmore High | 'Some text' |
*------------------------------------------------------*