如何从另一个表中获取引用值?

Not being this my job, I still have only very little knowledge of SQL, my question is to get directions on how to approach this problem. I hope the question is not too generic or meaningless:

由于不是我的工作，我对SQL的了解仍然很少，我的问题是如何解决这个问题。我希望这个问题不要太笼统或毫无意义:

I have several tables in a db. Few of them are basic tables, list of countries, list of customers and so on. These basic tables are used to support values in dropDownLists on other grids where users use preset values for certain fields and they also fill in other fields manually. Something like this:

我在数据库中有几个表。其中很少有基本的表格、国家列表、客户列表等。这些基本表用于支持其他网格上的下拉列表中的值，在这些下拉列表中，用户对某些字段使用预置值，并且他们还手动填充其他字段。是这样的:

Now, I am working on a dynamic report that points to the table where I need to get the actual values and not the references.

现在，我正在编写一个动态报告，该报告指向需要获取实际值而不是引用的表。

As I said, the problem I have is that those preset values in that particular table I am working on, are id_references (id_Country, id_Customer, ..) while I need the actual value they represent. What should I do to get the actual values? If I point my report to different tables I loose the meaning/link between the records.

正如我所说，我遇到的问题是，我正在处理的那个特定表中的那些预置值是id_references (id_Country, id_Customer， .)，而我需要它们所表示的实际值。我应该怎么做才能得到实际的值?如果我把报告指向不同的表，我就失去了记录之间的意义/链接。

1 个解决方案

#1

For Example if you are trying to get A country's Name by using Country_ID in this Project table shown in your question, You could do something like

例如，如果您试图在问题中显示的项目表中使用Country_ID来获取一个国家的名称，您可以执行以下操作

SELECT C.CountryName_Colummn, C.SomeOtheColumn, ......
FROM Projects P INNER JOIN Countries C
ON P.id_Country = C.id_Country
WHERE SomeColum = 'Your Condition'

1) SELECT Clause you select the columns you need in your result set.
2) FROM you Mention table(s) name(s)
3) ON Clause you define the relationship between the tables Say Projects table had a Column id_Country which refers to id_Country in Countries table defines the relationship between these two tables.
4) After you have Selected the column list, Source of data (tables), Their relationship (On Clause) then you can filter the number of rows returning from you query, like you can something like id_Country
WHERE C.CountryName = 'UK' will return results only from UK.
5) In from Clause the Letters 'C' and 'P' are alias so we dont have to type full table names again n again, makes our code simpler and easier to read and debug.

1) SELECT子句选择结果集中需要的列2)FROM you Mention table name(s) 3) ON Clause you define the relationship between tables假设Projects表有一个id_Country列，在Countries表中引用id_Country，定义了这两个表之间的关系。4)在您选择了列列表、数据源(表)、它们的关系(On子句)之后，您就可以筛选从查询返回的行数，就像您可以选择id_Country，其中包含C。CountryName = 'UK'只返回来自英国的结果。5)在from子句中，字母“C”和“P”是别名，这样我们就不必再次键入完整的表名n，使我们的代码更简单、更易于读取和调试。

No matter how many tables you have to join to get the required data as long as you can define the relationship between them table in your query it should work fine. SQL server rarely you would find all the required data in one table normally you would join 2-3 tables or more tables. For example you want some data that is present in 3 different tables you would join them all three in one query something like this ...

无论您需要连接多少个表来获取所需的数据，只要您能够在查询中定义它们之间的关系，它就可以正常工作。SQL server很少会在一个表中找到所有需要的数据，通常会连接2-3个表或更多的表。例如，你想要一些数据在3个不同的表中，你会把它们都加入到一个查询中，像这样。

SELECT C.CountryName_Colummn, C.SomeOtheColumn, ......
FROM Projects P INNER JOIN Countries C
ON P.id_Country = C.id_Country
INNER JOIN Table_3_Name T3
ON T3.CommonColumn = P.SomeCommonColumn (it will be a common column between table 3 and Projects OR Countries)
WHERE SomeColum = 'Your Condition'

But you really need to look in joins as you can do different types of joins between tables, Hers in this example I used INNER JOIN which returns only the matching rows between all these tables, you could do LEFT JOIN or RIGHT JOIN.
LEFT JOIN returns all the rows from the table on LEFT side of the JOIN key word and only matching rows from other tables.
RIGHT JOIN returns all the rows from the on the right side of the JOIN key word and only mantching rows from other tables.

但是，您确实需要查看连接，因为您可以在表之间进行不同类型的连接，在这个示例中，我使用了内部连接，它只返回所有这些表之间的匹配行，您可以做左连接或右连接。左连接返回连接关键字左侧的表中的所有行，并且只匹配来自其他表的行。右连接返回连接关键字右边的所有行，并且只从其他表强制行。

Queries with only desired Columns

只包含所需列的查询

Select customers.customer_name, Products.Product_type, Manufacturers.Manuf_name 
from Projects inner join customers
on     customers.cust_id= Projects.proj_cust_id
inner join Products
on     Products.prod_id= Projects.proj_prod_id
inner join Manufacturers
on     Manufacturers.man_id= Projects.proj_man_id

Making use of Alias will give you exactly the same reuslt but easy to read code also try this ....

利用别名会给你同样的reuslt但易读的代码也试试这个....

Select C.customer_name, Prod.Product_type, M.Manuf_name 
from Projects Proj inner join customers C
on     C.cust_id= Proj.proj_cust_id
inner join Products Prod
on     Prod.prod_id= Proj.proj_prod_id
inner join Manufacturers M
on     M.man_id= Proj.proj_man_id