从多个表中选择说明书，具有可变输入 [英] Select statment from multiple tables, with variable input

问题描述

我有两个表： AREACODE 和 EquipmentNumber

  + ------------------------------------ + 
 | AreaCd | 
 | ------------------------------------ | 
 | areaID表示INT NOT NULL AUTO_INCREMENT | 
 |代码INT | 
 |名称char（30）| 
 |评论TEXT | 
 | PKEY（areaID表示）| 
 + ------------ + 
 + --- --------------------------------- + 
 | EqNum | 
 | ------------------------------------ | 
 | EqID INT NOT NULL AUTO_INCREMENT | 
 | areaID表示INT | 
 |号CHAR（5）| 
 |类型为CHAR（10）| 
 | PKEY（EqID）| 
 | FKEY（areaID表示）REF AREACODE（areaID表示）| 
 + ------------ + 
  

我要提取 EqNum.Number ，式。键入和 AreaCd.Code 。麻烦的是，该查询由输入从表格填充，因此搜索限制是可变的。我创建了3相似，这些单独的查询：

 选择数量，类型，代码EqNum，AreaCd+ 
表示EqNum.AreaId = AreaCd.AreaId和代码=+ INT nCode +;; 
 
选择数量，类型，代码EqNum，AreaCd+ 
表示EqNum.AreaId = AreaCd.AreaId和数量='+字符串sNumber +; 
 
选择数量，类型，代码EqNum，AreaCd+ 
表示EqNum.AreaId = AreaCd.AreaId和Type ='+字符串STYPE +; 
  

所有做工精细自己，只要用户只搜索一列（代码，编号，或的键入）在一段时间，但我需要一次搜索一个，两个，<青霉>或的所有三列的能力。

我已经尝试使用或， LIKE ，多选择，我甚至试过铸造 INT nCode 为char使用％通配符，但我可以' 。找不到东西的作品

问：有人可以帮助我加入这三个查询搜索的表的所有三个felds的任意组合： EqNum.Number ， EqNum.Type 和 AreaCd.Code 将细化搜索结果当更多的字段添加？（即用于 EqNum.Type 的搜索将取得更多成果比搜索 EqNum.Number ， EqNum.Type 和 AreaCd.Code ）

解决方案

<预类=郎-SQL prettyprint-覆盖> SELECT e.Number，e.Type，a.Code
起价EqNumêINNER JOIN AreaCd一个
关于e.AreaId = a.AreaId
WHERE（@number为空或e.Number = @number）
和（@Type为空或e.Type = @Type）
和（@code为空或a.Code = @code）


要了解如何使用参数与ADO.NET，点击。这里

设置参数看起来是这样的：

  command.Parameters [@数]。值=（string.IsNullOrEmpty（数字）？ （对象）DBNull.Value：号码）; 
  

I have two tables: AreaCode and EquipmentNumber.

+------------------------------------+
| AreaCd                             |
|------------------------------------|
| AreaID INT NOT NULL AUTO_INCREMENT |
| Code INT                           |
| Name CHAR(30)                      |
| Comments TEXT                      |
| PKEY (AreaID)                      |
+------------------------------------+
+------------------------------------+
| EqNum                              |
|------------------------------------|
| EqID INT NOT NULL AUTO_INCREMENT   |
| AreaID INT                         |
| Number CHAR(5)                     | 
| Type CHAR(10)                      |
| PKEY (EqID)                        |
| FKEY (AreaID) REF AreaCode(AreaID) |
+------------------------------------+

I want to extract EqNum.Number, Eq.Type and AreaCd.Code. The trouble is, that the query is populated by input from a form, so the search restrictions are variable. I have created 3 seperate queries similar to these:

"SELECT Number, Type, Code FROM EqNum, AreaCd " +
"WHERE EqNum.AreaId = AreaCd.AreaId AND Code = " + int nCode + ";";

"SELECT Number, Type, Code FROM EqNum, AreaCd " +
"WHERE EqNum.AreaId = AreaCd.AreaId AND Number = '" + string sNumber + "';";

"SELECT Number, Type, Code FROM EqNum, AreaCd " +
"WHERE EqNum.AreaId = AreaCd.AreaId AND Type = '" + string sType + "';";

Which all work fine on their own, provided the user is only searching for one column (Code, Number, or Type) at a time, but I need the capability to search for one, two, or all three columns at once.

I have tried using OR, LIKE, multiple selection, I've even tried casting int nCode as a char to use the % wildcard, but I just can't find something that works.

QUESTION: Can someone help me join these three queries to search the tables for any combination of all three felds: EqNum.Number, EqNum.Type and AreaCd.Code that will refine the search result when more fields are added? (i.e. a search for EqNum.Type will yield more results than a search for EqNum.Number, EqNum.Type and AreaCd.Code)

解决方案

SELECT e.Number, e.Type, a.Code
FROM EqNum e INNER JOIN AreaCd a
ON e.AreaId = a.AreaId
WHERE (@Number IS NULL OR e.Number = @Number)
AND (@Type IS NULL OR e.Type = @Type)
AND (@Code IS NULL OR a.Code = @Code)

To learn how to use parameters with ADO.NET, click here.

Setting parameters would look something like this:

command.Parameters["@Number"].Value = (string.IsNullOrEmpty(number) ? (object) DBNull.Value : number);

这篇关于从多个表中选择说明书，具有可变输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！