如何编写 SQL select 语句以包含从另一个表进行的查找？

发布于 2024-07-18 09:04:34 字数 526 浏览 3 评论 0原文

我正在将数据从一个数据库复制到另一个数据库，并在操作过程中对数据进行调整。这两个数据库都有名为 Clients 和 Jobs 的表。

但是，在数据库“Alpha”中，Jobs 表与 Clients 表没有关系，而数据库“Epsilon”则有关系。 Alpha 的作业表仅在 nvarchar 列中包含客户名称。

当我将客户端插入 Epsilon 中的作业表时，我需要一个 select 语句来通过名称在客户端表中查找客户端的 ID。

我未完成的 SQL 语句如下所示：

insert into Epsilon.dbo.Jobs (ClientId, Name, Location, DateCreated)
    select ????, Name, Location, DateCreated from Alpha.dbo.Jobs

如何修改它以便 ???? 包含 Epsilon 中客户端表中的 ClientId？我知道我需要使用“作业”中的“名称”列查找数据，但我无法弄清楚其语法。

原文

I'm copying data from one database to another and massaging the data while I'm at it. Both databases have tables called Clients and Jobs.

However, in database "Alpha" the Jobs table does not have a relationship to the Clients table, where database "Epsilon" does. Alpha's Jobs table just has the Clients name in an nvarchar column.

I need a select statement to lookup the Client's ID in the Client table by their name while I am inserting it into the Jobs table in Epsilon.

My unfinished SQL statement looks like this:

insert into Epsilon.dbo.Jobs (ClientId, Name, Location, DateCreated)
    select ????, Name, Location, DateCreated from Alpha.dbo.Jobs

How can I modify this so that the ???? contains the ClientId from the Clients table in Epsilon? I know I need to lookup the data using the Name column in Jobs, but I can't figure out the syntax for this.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

夜无邪 2024-07-25 09:04:34

您需要的是加入。与几乎每个人开始时的想法相反，联接不需要在数据库模式中定义关系。它们只要求您要比较的两列具有相同的类型（编辑请参阅注释）。

问题是您想要加入哪个。由于没有定义关系，因此可能存在有工作的客户和没有工作的客户，以及有客户的工作和没有工作的工作。

我假设您希望所有存在的作业，并且 ClientId 与 CLIENTS 表匹配的地方引入 ClientId，并且如果该关系不存在，则将 ClientId 保留为空。我们可以通过 LEFT JOIN 来做到这一点。作业 LEFT JOIN 客户端将引入左侧的所有记录，即使与右侧客户端定义的关系不存在。我们可以颠倒两者并进行 RIGHT JOIN，但这不是人们通常所做的。我将让您阅读其他类型的联接及其工作原理。

因此，您的 select 语句如下所示：

select ClientId, Name, Location, DateCreated 
from Alpha.dbo.Jobs as J LEFT JOIN 
    Alpha.dbo.Clients as C ON j.ClientName = c.ClientName

如果 Jobs.ClientName 与 c.ClientName 的数据类型不同，您可以在运行查询之前编辑架构，以使它们彼此一致。

What you need is a join. Joins, contrary to what pretty much everybody thinks when starting out, don't require defined relationships in the schema of the database. They just require that the two columns you're comparing have the same type (edit see comments).

The question is which join do you want. Because there isn't a relationship defined, there may be clients that have jobs and clients that don't, and jobs that have clients and jobs that don't.

I'm assuming that you want all JOBS that exist, and where a ClientId matches the CLIENTS table bring in the ClientId, and where that relationship doesn't exist to leave the ClientId null. We can do this with a LEFT JOIN. Jobs LEFT JOIN Clients will bring in all records on the LEFT, even where the relationship defined with Clients on the right doesn't exist. We could reverse the two and do a RIGHT JOIN, but that's not what people usually do. I'll leave it to you to read up on other types of joins and how they work.

So your select statement would look like:

select ClientId, Name, Location, DateCreated 
from Alpha.dbo.Jobs as J LEFT JOIN 
    Alpha.dbo.Clients as C ON j.ClientName = c.ClientName

If Jobs.ClientName is not the same data type as c.ClientName, you can edit the schema before running the query to bring them in line with each other.

回复收藏 0 原文

浮生未歇 2024-07-25 09:04:34

insert into Epsilon.dbo.Jobs (ClientId, Name, Location, DateCreated)
    select c.ClientID, a.Name, a.Location, a.DateCreated from Alpha.dbo.Jobs a
    join Epsilon.dbo.Client c on c.Name = a.ClientName

这是一个非常乐观的连接，但即使需要修改，这也应该给您一个总体思路。

insert into Epsilon.dbo.Jobs (ClientId, Name, Location, DateCreated)
    select c.ClientID, a.Name, a.Location, a.DateCreated from Alpha.dbo.Jobs a
    join Epsilon.dbo.Client c on c.Name = a.ClientName

This is a pretty optimistic join, but even if it needs to be modified this should give you the general idea.

回复收藏 0 原文