Solved

SQL SERVER 2000 - Searching Inside A Table That Is Inner Joined With A Sub Table Returns Duplicates

Posted on 2007-12-04
17
171 Views
Last Modified: 2008-02-01
Experts,

Ive got a query that searches inside a table called Questions. This table is linked to QuestionCategories, in a one to many relation.

I want the user to be able to search in the questions, but my problem is that it returns the same record in duplicates depending on how many categories is this question related to.

The search is open, it searches for the same keyword in all fields, even the category name that is in a different table.

How may I achieve returning a result set that meets the users search but dont show the same record more than once.

I am attaching my stored procedure.

Thanks in advance



CREATE PROCEDURE [FAQ_OpenSearch]
(
	@keyword nvarchar(100)
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Err int
 
 
 
	SELECT    Q.Module_IKey,Q.IKey,Q.QuestionTitle, Q.QuestionHTML, C.CategoryName, S.SoftwareManufacturer, S.ProductName AS S_ProductName, S.SoftwareVersion, 
                      S.OtherDetails AS S_OtherDetails, H.HardwareManufacturer, H.ProductName AS H_ProductName, H.ProductModel, H.OtherDetails AS H_OtherDetails, 
                      D.[Language], D.Browser, D.URL, E.ErrorMessage, O.RelatedInformation, U.FullName
	FROM         dbo.FAQ_Question Q INNER JOIN
                      dbo.sys_Users U ON Q.EditedBy = U.IKey INNER JOIN
                      dbo.FAQ_QuestionCategories QC ON Q.IKey = QC.Question_IKey INNER JOIN
                      dbo.FAQ_Categories C ON QC.Category_IKey = C.IKey LEFT OUTER JOIN
                      dbo.FAQ_QuestionTags_Hardware H ON Q.IKey = H.Question_IKey LEFT OUTER JOIN
                      dbo.FAQ_QuestionTags_Software S ON Q.IKey = S.Question_IKey LEFT OUTER JOIN
                      dbo.FAQ_QuestionTags_Development D ON Q.IKey = D.Question_IKey LEFT OUTER JOIN
                      dbo.FAQ_QuestionTags_OtherInformation O ON Q.IKey = O.Question_IKey LEFT OUTER JOIN
                      dbo.FAQ_QuestionTags_ErrorMessages E ON Q.IKey = E.Question_IKey
 
 
	WHERE
		(
			 	CategoryName			LIKE '%' + @keyword + '%'
			OR 	QuestionHTML			LIKE '%' + @keyword + '%'
			OR 	QuestionTitle			LIKE '%' + @keyword + '%'
			OR 	RelatedInformation		LIKE '%' + @keyword + '%'
			OR 	S.OtherDetails			LIKE '%' + @keyword + '%'
			OR 	SoftwareVersion			LIKE '%' + @keyword + '%'
			OR 	S.ProductName			LIKE '%' + @keyword + '%'
			OR 	SoftwareManufacturer		LIKE '%' + @keyword + '%'
			OR 	H.OtherDetails			LIKE '%' + @keyword + '%'
			OR 	ProductModel			LIKE '%' + @keyword + '%'
			OR 	H.ProductName			LIKE '%' + @keyword + '%'
			OR 	HardwareManufacturer		LIKE '%' + @keyword + '%'
			OR 	ErrorMessage			LIKE '%' + @keyword + '%'
			OR 	URL				LIKE '%' + @keyword + '%'
			OR 	Browser				LIKE '%' + @keyword + '%'
			OR 	Language			LIKE '%' + @keyword + '%'
	
		)
	
 
 
	ORDER BY Q.SystemDate DESC
 
 
	SET @Err = @@Error
	RETURN @Err
END
GO

Open in new window

0
Comment
Question by:feesu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20402147
try making line 12 SELECT DISTINCT
0
 

Author Comment

by:feesu
ID: 20402168
I have an ntext field that doesn't accept a DISTINCT selection!
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20402175
does it belong to any of these four tables?
dbo.FAQ_Question Q
dbo.sys_Users U
dbo.FAQ_QuestionCategories QC
dbo.FAQ_Categories C
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:feesu
ID: 20402179
It's the QuestionHTML field. Belongs to dbo.FAQ_Question Q
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20402188
The other question I have, is if it is found in more than one category, which CategoryName do you show?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20402231
Is the FAQ_QuestionTags_Software also many to one with Question?
What if it matches two FAQ_QuestionTags_Software records, doesn't that also show the question more than once?
0
 

Author Comment

by:feesu
ID: 20402238
If the keyword is in more than one category then let it show the first one.
Regarding ntext, i don't think that you need to worry about it cuz i will be using the function you sent me in another thread that converts html to text and will not be ntext anymore, but the question is, will calling that function such as dbo.ufn_removetags(fieldname) LIKE '%' + @Keyword + '%" will accept DISTINCT?
0
 

Author Comment

by:feesu
ID: 20402258
BTW, i posted the errors i got while trying to create that HTML function on the other thread.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20402274
do you ever need to return more than 4000 chars from ANY column to the end user??  If not, ntext is too large, consider using CONVERT(nVarchar(4000), columnname) as ShorterColumn in the SELECT, and then you can use distinct
0
 
LVL 25

Accepted Solution

by:
imitchie earned 250 total points
ID: 20402349
Assuming QuestionHTML is your only NTEXT field, this query ensures that a Question is only ever listed once, regardless of the many one-many relationshipts.
CREATE PROCEDURE [FAQ_OpenSearch]
(
        @keyword nvarchar(100)
)
AS
BEGIN
        SET NOCOUNT ON
        DECLARE @Err int
 
 
 	SELECT	Q.Module_IKey,Q.QuestionTitle, Q.QuestionHTML, INNERQ.*
	FROM
	(
	SELECT    Q.IKey,
		MIN(C.CategoryName) AS CategoryName,
		MIN(S.SoftwareManufacturer) AS SoftwareManufacturer,
		MIN(S.ProductName) AS S_ProductName,
		MIN(S.SoftwareVersion) AS SoftwareVersion, 
                MIN(S.OtherDetails) AS S_OtherDetails,
		MIN(H.HardwareManufacturer) AS HardwareManufacturer,
		MIN(H.ProductName) AS H_ProductName,
		MIN(H.ProductModel) AS ProductModel,
		MIN(H.OtherDetails) AS H_OtherDetails, 
		MIN(D.[Language]) AS Language,
		MIN(D.Browser) AS Browser,
		MIN(D.URL) AS URL,
		MIN(E.ErrorMessage) AS ErrorMessage,
		MIN(O.RelatedInformation) AS RelatedInformation,
		MIN(U.FullName) as FullName
        FROM         dbo.FAQ_Question Q INNER JOIN
                      dbo.sys_Users U ON Q.EditedBy = U.IKey INNER JOIN
                      dbo.FAQ_QuestionCategories QC ON Q.IKey = QC.Question_IKey INNER JOIN
                      dbo.FAQ_Categories C ON QC.Category_IKey = C.IKey LEFT OUTER JOIN
                      dbo.FAQ_QuestionTags_Hardware H ON Q.IKey = H.Question_IKey LEFT OUTER JOIN
                      dbo.FAQ_QuestionTags_Software S ON Q.IKey = S.Question_IKey LEFT OUTER JOIN
                      dbo.FAQ_QuestionTags_Development D ON Q.IKey = D.Question_IKey LEFT OUTER JOIN
                      dbo.FAQ_QuestionTags_OtherInformation O ON Q.IKey = O.Question_IKey LEFT OUTER JOIN
                      dbo.FAQ_QuestionTags_ErrorMessages E ON Q.IKey = E.Question_IKey
 
 
        WHERE
                (
                                CategoryName                    LIKE '%' + @keyword + '%'
                        OR      QuestionHTML                    LIKE '%' + @keyword + '%'
                        OR      QuestionTitle                   LIKE '%' + @keyword + '%'
                        OR      RelatedInformation              LIKE '%' + @keyword + '%'
                        OR      S.OtherDetails                  LIKE '%' + @keyword + '%'
                        OR      SoftwareVersion                 LIKE '%' + @keyword + '%'
                        OR      S.ProductName                   LIKE '%' + @keyword + '%'
                        OR      SoftwareManufacturer            LIKE '%' + @keyword + '%'
                        OR      H.OtherDetails                  LIKE '%' + @keyword + '%'
                        OR      ProductModel                    LIKE '%' + @keyword + '%'
                        OR      H.ProductName                   LIKE '%' + @keyword + '%'
                        OR      HardwareManufacturer            LIKE '%' + @keyword + '%'
                        OR      ErrorMessage                    LIKE '%' + @keyword + '%'
                        OR      URL                             LIKE '%' + @keyword + '%'
                        OR      Browser                         LIKE '%' + @keyword + '%'
                        OR      Language                        LIKE '%' + @keyword + '%'
        
                )
	GROUP BY Q.IKEY        
 	) INNERQ INNER JOIN dbo.FAQ_Question Q ON Q.IKEY = INNERQ.IKEY
		
        ORDER BY Q.SystemDate DESC
 
 
        SET @Err = @@Error
        RETURN @Err
END
GO

Open in new window

0
 

Author Comment

by:feesu
ID: 20407263
imitchie,
You didn't comment on my previous comment. I was asking that if i will use the remove tag function, then i won't need the ntext field cuz that function will return 4000 chars.

Do i still need to use the SP you modified or you will change it now?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20407352
feesu,

Use http:#20402349
Even if the NText problem is solved, Distinct doesn't solve the problem of one question being matched in two categories

i.e.
Question, Category
What is the matrix, Matrix
What is the matrix, Movie

Distinct will still leave the question in the result set twice
0
 

Author Comment

by:feesu
ID: 20409283
imitchie,
This worked perfectly, but i have one observation though, i need to search and exclude html but while displaying my search results in a grid i need it to at least have those breaks between lines that make it look kinda readable because this way the ntext paragraph appears as a whole block of text in the grid!
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20409565
Can you elaborate a bit on that? Do you have a sample of what a HTML block looks like from your table? Do you mean that you need <BR/> tags in there to cause the browser to put line breaks in?
0
 

Author Comment

by:feesu
ID: 20409937
Well, the reason behind saving the html code is to display it in a formated way, and edit it using a rich text editor. When searching, i have to exclude the tags to get accurate results, but then if the select statement has my column value in a plain text format, it won't display properly on my page.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20409996
How do you know where the line breaks should be? Are they in there as char(13) and/or char(10)?
0
 

Author Comment

by:feesu
ID: 20418227
It's not only about breaks, that was just an example. I solved it by searching with a query, and binding the grid to another one that is passed the condition. Thanks.
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question