Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  Getting duplicate values when joining

Author  Topic  n3tx Starting Member 3 Posts Posted - 2009-11-09 : 21:13:30 Hello, I am having some problems with a search query. The problem I am having is that I get duplicate values when I am joining two tables. Everything worked fine until I joined a second table (Addresses) Table Companies (ID (unique), CompanyName, Orgnumber ...) Table Addresses (Company_ID (not unique), Address .. ) -- table can have more than one address with the same Company_ID Here is my sp: ALTER PROCEDURE [dbo].[sp_GetCompaniesByKeywords] @KeyWhat varchar(150) = NULL, -- what to search for @KeyWhere varchar(150) = NULL, -- where to search @PageSize int = NULL, -- page size @PageNumber int = NULL -- current page AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @RowStart int DECLARE @RowEND int IF (@PageNumber > 0) BEGIN IF (@PageNumber <=1) SET @RowEND = @RowStart + @PageSize +1 SET @PageNumber = @PageNumber -1 SET @RowStart = @PageSize * @PageNumber+1 SET @RowEND = @RowStart + @PageSize - 1; DECLARE @sql nvarchar(4000) SELECT @sql = 'Select DISTINCT ROW_NUMBER() OVER (ORDER BY C.[Name]) AS RowNumber, COUNT(*) OVER() AS Count, c.ID, C.[Name] AS CompanyName, C.Orgnumber AS orgnr, cat.Name AS Genre FROM Companies as C JOIN Categories AS cat ON cat.ID = c.Genre_ID JOIN Addresses AS a ON a.CompanyID = C.ID WHERE 1=1' IF (@KeyWhat IS NOT NULL AND @KeyWhat <> '') SELECT @sql = @sql + ' AND C.[Name] LIKE ' + quotename('%' + @KeyWhat + '%', '''') + ' OR cat.[Name] LIKE ' + quotename('%' + @KeyWhat + '%', '''') + ' OR C.Orgnumber LIKE ' + quotename(@KeyWhat + '%', '''') IF (@KeyWhere IS NOT NULL AND @KeyWhere <> '') SELECT @sql = @sql + ' AND a.address LIKE ' + quotename('%' + @KeyWhere + '%','''') + ' OR a.zipcode LIKE ' + quotename('%' + @KeyWhere + '%','''') + ' OR a.county LIKE ' + quotename('%' + @KeyWhere + '%','''') + ' OR a.othertext LIKE ' + quotename('%' + @KeyWhere + '%','''') --PRINT @sql CREATE TABLE #Result ( RowNumber int, [Count] int, id int, companyName varchar(150), orgnumber varchar(20), kategori varchar(100) ) INSERT INTO #Result(rownumber, [count], id, companyname, orgnumber, kategori) EXEC sp_executeSQL @sql SELECT * From #Result WHERE RowNumber Between @RowStart and @RowEND DROP Table #Result END END // Daniel