连接3个表并过滤SQL [英] Joining 3 tables and filtering SQL

问题描述

我正在使用AdvetureWorks数据库.这是我想做的: 给我姓氏，名字，公司名称，并列出所有客户Virginia Miller的地址，包括地址类型，地址，城市和州. 您需要将3个表连接在一起并过滤结果.您应该只获得2行. 这就是我到目前为止所拥有的...

I am using the AdvetureWorks database. Here is what I would like to do: Give me the Last Name, First Name, company name and list me all the addresses for customer Virginia Miller, include address type, address, city and state. You need to join 3 tables together and filter the results. You should only get 2 rows. This is what I have so far...

Select
SalesLT.Customer.LastName,
SalesLT.Customer.FirstName,
SalesLT.Customer.CompanyName,
CustomerAddress.AddressType,
SalesLT.Address.AddressLine1,
SalesLT.Address.City,
SalesLT.Address.StateProvince

From SalesLT.Customer C, SalesLT.CustomerAddress CA, SalesLT.Address A


 join SalesLT.CustomerAddress

on SalesLT.Address.AddressID=SalesLT.CustomerAddress.AddressID


join SalesLT.Customer
on SalesLT.CustomerAddress.CustomerID=SalesLT.Customer.CustomerID

join SalesLT.Address
on SalesLT.CustomerAddress.AddressID=SalesLT.Address.AddressID

Where SalesLT.Customer.FirstName = 'Virginia'

是的，我是新手，不了解的人加入得很好.任何朝着正确方向推动的人都会受到赞赏！

Yeah I am new and not understanding joins very well. Any nudges in the correct direction are much appreciated!

推荐答案

select语句的重点是以特定方式获取一些数据.大致采用以下形式:

The point of a select statement is to get some data back in a specific way. This takes roughly the following form:

select <columns>
from <table/group of tables joined together/sub-query/tvf etc.>
where <condition>

1-现在，您遇到问题的地方似乎是中间的地方-我们首先要做.

1 - Now, the bit you've having the problem with seems to be the middle bit - we'll do that first.

您有三个表，其中包含要返回的数据:

You've got three tables which contain the data you want back in:

SalesLT.Customer 
SalesLT.CustomerAddress 
SalesLT.Address

您想将它们连接到数据相互关联的列上，在本例中为CustomerID.内部联接语句采用以下形式(选择内部联接，因为所有条目在其他表中都有匹配项！):

And you want to join them together on the columns where the data relate to each other, in this case CustomerID. The inner join statement takes the following form (choosing inner join since all entries have a match in the other tables!):

table1 inner join table2 on table1.columnname = table2.columnname

如果我们将您的示例与该语法匹配，我们将得到以下信息:

and if we match up your example to this syntax, we get this:

SalesLT.CustomerAddress INNER JOIN
                      SalesLT.Customer ON SalesLT.CustomerAddress.CustomerID = SalesLT.Customer.CustomerID INNER JOIN
                      SalesLT.Address ON SalesLT.CustomerAddress.AddressID = SalesLT.Address.AddressID

现在，其余的select语句将这个查询视为一个大的数据集合"，几乎就像是一个表一样-只有每列都以表名为前缀.在语句中引用其他位置的地方，不必总是包含完全限定的名称(只要可以猜测，只要结果集中有不止一个具有相同名称的列即可).

Now the rest of the select statement sees this query as one big "collection" of data, almost as if it was one table - only each column is pre-fixed by the table name. Where you reference one elsewhere in the statement it isn't always necessary to include the fully qualified name (as it can guess as long as there isn't more than one column with the same name in the result set).

2-您似乎掌握了过滤where子句，但是尽管您有最初的要求，但您似乎只是对名字进行过滤.添加另外一个很容易:

2 - The filtering where clause you seem to have got the hang of, but you only seem to be filtering on first name, despite your initial requirements. Adding an additional one is easy:

WHERE     (SalesLT.Customer.FirstName = 'Virginia') AND (SalesLT.Customer.LastName = N'Miller')

3-最后，您要返回的内容有个小问题-您已经正确了.

3 - Finally there is the small matter of what you want back - this you already had correct.

结合我们共同完成的工作，您将获得以下查询:

Combining what we've done together, you get the following query:

SELECT     SalesLT.Customer.LastName, SalesLT.Customer.FirstName, SalesLT.Customer.CompanyName, SalesLT.CustomerAddress.AddressType, 
                      SalesLT.Address.AddressLine1, SalesLT.Address.City, SalesLT.Address.StateProvince
FROM         SalesLT.CustomerAddress INNER JOIN
                      SalesLT.Customer ON SalesLT.CustomerAddress.CustomerID = SalesLT.Customer.CustomerID INNER JOIN
                      SalesLT.Address ON SalesLT.CustomerAddress.AddressID = SalesLT.Address.AddressID
WHERE     (SalesLT.Customer.FirstName = 'Virginia') AND (SalesLT.Customer.LastName = N'Miller')

这篇关于连接3个表并过滤SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！