Jag håller på med ett diskussionsforum till ett informationssystem och den innehåller förståss en sökfunktion. Problemet är dock att den loopar ut resultatet TRE (!) gånger ur databasen..! -suck-

Koden ser ut så här:

SELECT Forum_SubGroups.Id, Forum_SubGroups.Name, Forum_SubGroups.Comment, Addressbook.Addressbook_FirstName, Addressbook.Addressbook_LastName, Count(Forum_Replies.Id) AS AntalförId, Max(Forum_Topic.CreationDate) AS MaxförCreationDate, Forum_Groups.BgColor, Forum_Groups.BorderColor, Forum_Groups.Name, Addressbook.Addressbook_Id FROM ((Division INNER JOIN [User] ON Division.Division_Id = User.User_DivisionID) INNER JOIN ((Forum_Groups INNER JOIN (Forum_SubGroups INNER JOIN Addressbook ON Forum_SubGroups.UserID = Addressbook.Addressbook_UserID) ON Forum_Groups.Id = Forum_SubGroups.GroupID) LEFT JOIN Forum_Topic ON Forum_SubGroups.Id = Forum_Topic.SubGroupID) ON Division.Division_Id = Forum_Groups.DivisionID) LEFT JOIN Forum_Replies ON Forum_Topic.Id = Forum_Replies.TopicID GROUP BY Forum_SubGroups.Id, Forum_SubGroups.Name, Forum_SubGroups.Comment, Addressbook.Addressbook_FirstName, Addressbook.Addressbook_LastName, Forum_Groups.BgColor, Forum_Groups.BorderColor, Forum_Groups.Name, Addressbook.Addressbook_Id, User.User_Id, Forum_Topic.Subject, Forum_Topic.Message, Forum_Replies.Message

Vad är det jag missar???

Tacksam för hjälp...

Det är totalt omöjligt att läsa en så lång SQL-sats utan rabrytningar... Utan att ha studerat det närmare så tror jag att det är en join som ställer till det.

/Johan

Du länkar grupp mot divesion mot användare. Med andra ord kommer resultatet att visas lika många gånger som det finns användare under gruppens divesion.

Har ingen anning om vad divessionstabellen är till för. Men tror du skall länka användarna till inläggen för att visa vem som skrivit det.

Hur ser din databasstrukltur ut?
Vad vill du ha ut?

Hmmm... Vad jag vill få ut är ETT enda sökresultat men, som sagt, nu blir det flera.

Så här ser det egentligen ut (det är MYCKET):

<code>
If (iTopicIdSearch > 0) Then


sSqlString = "SELECT "
sSqlString = sSqlString + "Forum_SubGroups.Id, "
sSqlString = sSqlString + "Forum_SubGroups.Name, "
sSqlString = sSqlString + "Forum_SubGroups.Comment, "
sSqlString = sSqlString + "Addressbook.Addressbook_FirstName, "
sSqlString = sSqlString + "Addressbook.Addressbook_LastName, "
sSqlString = sSqlString + "Count(Forum_Replies.Id) AS AntalförId, "
sSqlString = sSqlString + "Max(Forum_Replies.CreationDate) AS MaxförCreationDate, "
sSqlString = sSqlString + "Forum_Groups.BgColor, "
sSqlString = sSqlString + "Forum_Groups.BorderColor, "
sSqlString = sSqlString + "Addressbook.Addressbook_Id, "
sSqlString = sSqlString + "Forum_Groups.Name "
sSqlString = sSqlString + "FROM (Addressbook INNER JOIN ((Forum_Groups INNER JOIN Forum_SubGroups ON Forum_Groups.Id = Forum_SubGroups.GroupID) "
sSqlString = sSqlString + "LEFT JOIN Forum_Topic ON Forum_SubGroups.Id = Forum_Topic.SubGroupID) ON Addressbook.Addressbook_Id = Forum_SubGroups.AddressbookID) "
sSqlString = sSqlString + "LEFT JOIN Forum_Replies ON Forum_Topic.Id = Forum_Replies.TopicID "

If (sTopicTextSearch <> "") Then
sSqlString = sSqlString + "WHERE (((Forum_Topic.Subject) Like '%" & sTopicTextSearch & "%')) OR (((Forum_Topic.Message) Like '%" & sTopicTextSearch & "%')) OR (((Forum_Replies.Message) Like '%" & sTopicTextSearch & "%')) OR (((Forum_SubGroups.Name) Like '%" & sTopicTextSearch & "%')) OR (((Forum_SubGroups.Comment) Like '%" & sTopicTextSearch & "%')) "
sSqlString = sSqlString + "GROUP BY Forum_SubGroups.Id, Forum_SubGroups.Name, Forum_SubGroups.Comment, Addressbook.Addressbook_FirstName, Addressbook.Addressbook_LastName, Forum_Groups.BgColor, Forum_Groups.BorderColor, Addressbook.Addressbook_Id, Forum_Groups.Id, Forum_Groups.Name "
sSqlString = sSqlString + "HAVING (((Forum_Groups.Id)=" & iTopicIdSearch & "));"
Else
sSqlString = sSqlString + "WHERE (((Forum_Groups.Id)=" & iTopicIdSearch & ")) "
sSqlString = sSqlString + "GROUP BY Forum_SubGroups.Id, Forum_SubGroups.Name, Forum_SubGroups.Comment, Addressbook.Addressbook_FirstName, Addressbook.Addressbook_LastName, Forum_Groups.BgColor, Forum_Groups.BorderColor, Addressbook.Addressbook_Id, Forum_Groups.Name;"
End If

Else

sSqlString = "SELECT "
sSqlString = sSqlString + "Forum_SubGroups.Id, "
sSqlString = sSqlString + "Forum_SubGroups.Name, "
sSqlString = sSqlString + "Forum_SubGroups.Comment, "
sSqlString = sSqlString + "Addressbook.Addressbook_FirstName, "
sSqlString = sSqlString + "Addressbook.Addressbook_LastName, "
sSqlString = sSqlString + "Count(Forum_Replies.Id) AS AntalförId, "
sSqlString = sSqlString + "Max(Forum_Topic.CreationDate) AS MaxförCreationDate, "
sSqlString = sSqlString + "Forum_Groups.BgColor, "
sSqlString = sSqlString + "Forum_Groups.BorderColor, "
sSqlString = sSqlString + "Forum_Groups.Name, "
sSqlString = sSqlString + "Addressbook.Addressbook_Id "
sSqlString = sSqlString + "FROM ((Division INNER JOIN [User] ON Division.Division_Id = User.User_DivisionID) "
sSqlString = sSqlString + "INNER JOIN ((Forum_Groups INNER JOIN "
sSqlString = sSqlString + "(Forum_SubGroups INNER JOIN Addressbook ON "
sSqlString = sSqlString + "Forum_SubGroups.UserID = Addressbook.Addressbook_UserID) ON "
sSqlString = sSqlString + "Forum_Groups.Id = Forum_SubGroups.GroupID) LEFT JOIN Forum_Topic "
sSqlString = sSqlString + "ON Forum_SubGroups.Id = Forum_Topic.SubGroupID) ON "
sSqlString = sSqlString + "Division.Division_Id = Forum_Groups.DivisionID) "
sSqlString = sSqlString + "LEFT JOIN Forum_Replies ON Forum_Topic.Id = Forum_Replies.TopicID "
sSqlString = sSqlString + "GROUP BY Forum_SubGroups.Id, Forum_SubGroups.Name, "
sSqlString = sSqlString + "Forum_SubGroups.Comment, Addressbook.Addressbook_FirstName, "
sSqlString = sSqlString + "Addressbook.Addressbook_LastName, Forum_Groups.BgColor, "
sSqlString = sSqlString + "Forum_Groups.BorderColor, Forum_Groups.Name, Addressbook.Addressbook_Id, "
sSqlString = sSqlString + "User.User_Id, Forum_Topic.Subject, Forum_Topic.Message, Forum_Replies.Message "

If (sTopicTextSearch <> "") Then
sSqlString = sSqlString + "HAVING (((Forum_SubGroups.Name) Like '%" & sTopicTextSearch & "%') AND ((User.User_Id)=13)) "
sSqlString = sSqlString + "OR (((Forum_SubGroups.Comment) Like '%" & sTopicTextSearch & "%')) OR (((Forum_Topic.Subject) Like '%" & sTopicTextSearch & "%')) "
sSqlString = sSqlString + "OR (((Forum_Topic.Message) Like '%" & sTopicTextSearch & "%')) OR (((Forum_Replies.Message) Like '%" & sTopicTextSearch & "%')); "
End If


End If

response.write (sSqlString)

Set oRs = oCon.Execute(sSqlString)
If (not oRs.eof) Then
aSubGroup = oRs.getRows
Else
aSubGroup = null
End If
</code>

Inte lätt alltså att hitta strulet.