查询多个关系表中的数据 [英] Query data from multiple Relational Tables

问题描述

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

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

推荐答案

你可以做的是：

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

我认为这是你想要的.....

I think this is what u want.....

这篇关于查询多个关系表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！