SQL连接和逻辑 [英] SQL joins and logic

问题描述

我正在使用C#设计的SharePoint Web部件.我需要查询SQL Server 2005数据库以返回与由用户选择的特定客户关联的计算机的名称.

I am working with a SharePoint web part that I am designing in C#. I need to query a SQL Server 2005 database to return the names of machines that are associated with a particular customer, selected by the user.

问题在于计算机的类型不同，名称本身存储在其他几个表中.这是与此问题相关的结构:

The problem is that there are different types of machines, and the names themselves are stored in several other tables. Here is the structure as it pertains to this question:

我有一个表"customerInfo"，其中包含每个客户的ID和名称. ID是主键.

I have a table "customerInfo" which contains the ID and name of each customer. The ID is the primary key.

表"machineIDs"将客户ID链接到相应的机器ID.机器ID是主键.

A table "machineIDs" links the customer IDs to the corresponding machine IDs. The machine ID is the primary key.

我有两个表，分别用于两种类型的机器:"machine1"和"machine2". machine1包含计算机本身的名称，但是machine2仅包含其所在计算机类型的ID.在这两个表中，计算机ID都是主键.

I have two tables for two types of machines, "machine1" and "machine2." machine1 contains the name of the machine itself, but machine2 only contains the ID of the type of machine it is. With both of these tables, the machine ID is the primary key.

然后我有一个表"specialtyMachine".该表包含计算机的名称和这种类型的计算机的ID.这里的主键是类型ID，它标识机器的类型，而机器ID标识特定的机器.

Then I have a table "specialtyMachine." This table contains the name of the machine and the ID of this type of machine. The primary key here is a type ID, which identifies the type of machine, whereas the machine ID identifies the specific machine.

我需要做的就是从机器1和specialityMachine中提取机器名称，仅给出客户ID.我不确定是否可以通过直接在查询中进行复杂的联接来做到这一点，或者我是否需要在Web部件中应用逻辑以将这些信息汇总在一起.

What I need to do is pull the machine name from machine1 and specialtyMachine, given just the customer ID. I'm not sure whether it's possible to do this with a complex join directly within the query, or if I will need to apply logic in the web part to pull this information together.

我还想知道如何根据通用模型对此进行思考.我希望能够认识到这一点并在将来将其通用化，因为我敢肯定这是一个相当普遍的问题，我尚未解决.谢谢.

I would also like to know how I can think about this in terms of a generic model. I'd like to be able to recognize this and apply it generically in the future, as I'm sure it's a fairly common problem that I just haven't dealt with yet. Thank you.

推荐答案

基于您的新架构信息，我猜这是您想要的:

Based on your new schema information I'm guessing this is what you want:

select *
from customerinfo c
left join machineids mids on c.customerid = mids.customerid
left join machine1 m1 on mids.machineid = m1.machineid
left join spcialtymachine sm on mids.machineid = sm.machineid -- or m1.machineid = sm.machineid
left join machine2 m2 on sm.typeid = m2.typeid

注意:如果您在以@customerid作为参数的存储过程中使用此参数，则可以在末尾添加where c.customerid = @customerid.

note: if you are using this in a stored procedure with an @customerid as a parameter you can add a where c.customerid = @customerid to the end.

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