我应该为此查询添加数据表吗? {未解决} [英] Should i add a datatable for this query? {NOT RESOLVED}

问题描述

朋友，


在我的数据库模式中，有以下内容:

tblQuery(问题)
tblAnswers
tblCustomers
tblAnswers
tblDesignations
tblOperators

在我的ERD上编码并没有任何桥接表来链接任何两个表.它们都是直接连接的.我需要检索所有查询的答案以及发送查询的客户.因此，我必须链接三个tbalCustomers，tblQueries和tblAnswers

这是我用于撤回一行的SP:

Hi friend,


In my DB schema i have the followng tbales:

tblQuery (Questions)
tblAnswers
tblCustomers
tblAnswers
tblDesignations
tblOperators

accoding my ERD there arent any bridging tables to link any two tables. all of them are directly connected. I need to retreive a Query with all it's answers and Customers who sent the query. therefore i have to link three tbales tblCustomers, tblQueries and tblAnswers

Here is my SP for retreiving a row:

ALTER PROCEDURE ProcGetCustomerWithQueries 

	@PCustIndex int

AS

BEGIN

	SELECT 

	C.CustIndex, C.FirstName, C.LastName, 

	Q.QID, Q.QText, Q.QCat, A.AnsID, A.AnsText

	FROM tblCustomers C, tblQueries Q, tblAnswers A

	WHERE 

	C.CustIndex = Q.CustIndex

	AND

	Q.QID = A.QID

	and 

	Q.CustIndex = @PCustIndex	 	

END

GO

推荐答案

首先-您应该使用"joins"而不是滥用where子句.它运行速度更快，并且通常是公认的使用方法-您的SQL代理将感谢您;))

SELECT
C.CustIndex，C.FirstName，C.LastName，
Q.QID，Q.QText，Q.QCat，A.AnsID，A.AnsText
FROM tblCustomers C
内部加入tblQueries Q on C.CustIndex = Q.CustIndex
内部加入tblAnswers A on Q.QID = A.QID

First and foremost - you should use "joins" instead of abusing the where clause.  It'll run faster, and is generally the accepted method to use - your SQL Agent will thank you ;)

 SELECT
C.CustIndex, C.FirstName, C.LastName,
Q.QID, Q.QText, Q.QCat, A.AnsID, A.AnsText
FROM tblCustomers C
inner join tblQueries Q on C.CustIndex = Q.CustIndex
inner join tblAnswers A on Q.QID = A.QID
WHERE Q.CustIndex = @PCustIndex  


If your other logic exists in stored procedures, make this one as well.  Stay within the standards that are in place until everything moves.  The only thing worse than having to develop against an outdated, legacy infrastrusture is having to develop against one that has implemented n + 1 standards over it's lifetime with no clear migration path.

In which case - don't use a datatable for this.  Use a DataReader object with some simple logical iterations (or if you're 3.5+ - LINQ.  2.0+ extension methods/predicates/actions) and use it thusly.

这篇关于我应该为此查询添加数据表吗? {未解决}的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！