php - 从多个关系表查询数据

Question

我试图在一个查询中从多个关系表中查询数据。我有一个收据表，它通过将 user_id 和 customer_id 存储为 FK 来保存客户购买的信息。我想在获取 user_name 和 customer_name 的同时查询整个收据列表。这可以/应该在单个查询中完成吗？

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|  
*---------------------------*

所以我希望我的结果集是这样的:

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'  |
*------------------------------------------------------* 

Answer 1

你可以做的是:

SELECT receipt_id,user_name,customer_name, receipt_info

FROM user u
INNER JOIN receipt r
on r.user_id = u.user_id
INNER JOIN customer c
on c.customer_id = r.customer_id

我想这就是你想要的......