SQL查询从一个表中获取基于另一个表的列的数据？

In my tables I have for example

在我的表中,我有例如

CountyID,County and CityID in the county table and in the city table I have table I have for example

CountyID,County和CityID在县表和城市表中我有我的表例如

City ID and City

城市ID和城市

How do I create a report that pulls the County from the county table and pulls city based upon the cityid in the county table.

如何创建一个报告,将县从县表中拉出来,并根据县表中的cityid拉取城市。

Thanks

3 个解决方案

#1

Since this is quite a basic question, I'll give you a basic answer instead of the code to do it for you.

由于这是一个非常基本的问题,我会给你一个基本答案,而不是为你做的代码。

Where tables have columns that "match" each other, you can join them together on what they have in common, and query the result almost as if it was one table.

如果表中的列具有彼此“匹配”的列,则可以将它们共同组合在一起,并查询结果,就像它是一个表一样。

There are also different types of join based on what you want - for example it might be that some rows in one of the tables you're joining together don't have a corresponding match.

根据您的需要,还有不同类型的连接 - 例如,您连接在一起的某个表中的某些行可能没有相应的匹配。

If you're sure that a city will definitely have a corresponding county, try inner joining the two tables on their matching column CityID and querying the result.

如果您确定某个城市肯定会有相应的县,请尝试在其匹配列CityID上加入两个表并查询结果。

#2

The obvious common link between both tables is CityID, so you'd be joining on that. I think you have the data organized wrong though, I'd put CountryID in the City table rather than CityID in the country table. Then, based on the CountryID selected, you can limit your query of the City table based on that.

两个表之间明显的共同点是CityID,所以你要加入。我认为你的数据组织有误,我将CountryID放在City表中而不是CountryID中的CityID。然后,根据所选的CountryID,您可以基于此限制对City表的查询。

#3

To follow in context of Bridge's answer, you are obviously new to SQL and there are many places to dig up how to write them. However, the most fundamental basics you should train yourself with is always apply the table name or alias to prevent ambiguity and try to avoid using column names that might be considered reserved words to the language... they always appear to bite people.

要了解Bridge的答案背景,你显然是SQL的新手,有很多地方可以挖掘如何编写它们。但是,您应该训练自己的最基本的基础知识是始终应用表名或别名以防止歧义,并尽量避免使用可能被视为语言保留字的列名...它们总是看起来像是咬人。

That said, the most basic of queries is

也就是说,最基本的查询是

select
      T1.field1,
      T1.field2,
      etc with more fields you want
   from
      FirstTable as T1
   where
      (some conditional criteria)
   order by
      (some column or columns)

Now, when dealing with multiple tables, you need the JOINs... typically INNER or LEFT are most common. Inner means MUST match in both tables. LEFT means must match the table on the left side regardless of a match to the right... ex:

现在,在处理多个表时,您需要JOIN ...通常INNER或LEFT是最常见的。内部意味着必须在两个表中匹配。 LEFT表示必须与左侧的表匹配,无论与右侧的匹配如何... ex:

select
      T1.Field1,
      T2.SomeField,
      T3.MaybeExistsField
   from
      SomeTable T1
         Join SecondTable T2
            on T1.SomeKey = T2.MatchingColumnInSecondTable
         LEFT JOIN ThirdTable T3
            on T1.AnotherKey = T3.ColumnThatMayHaveTheMatchingKey
   order by
      T2.SomeField DESC,
      T1.Field1

From these examples, you should easily be able to incorporate your tables and their relationships to each other into your results...

从这些示例中,您应该能够轻松地将表格及其关系相互结合到您的结果中......

#1