November 9, 2009 at 1:45 pm
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 query:
declare @KeyWhat varchar(100)
set @KeyWhat = 'daniel'
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
FROM Companies as C
LEFT OUTER JOIN Addresses AS a ON a.CompanyID = C.ID
WHERE 1=1
AND C.[Name] LIKE '%' + @KeyWhat + '%'
OR C.Orgnumber LIKE @KeyWhat + '%'
Thanks for your help!
Daniel
November 9, 2009 at 1:51 pm
Is the purpose of this query to get the number of addresses for a company?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2009 at 2:02 pm
No, I just want Company info like Company name, orgnumber only.
But you can search by address info like zipcode, street address etc.
but i haven't put that in the query yet, i just want the query to work with joins first. Before I put any more criterias in the where clause.
// Daniel
November 9, 2009 at 2:09 pm
Here's the whole sp, maybe you will understand better:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
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
November 9, 2009 at 2:12 pm
Change the Join to an In statement with a sub-query of the Address table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2009 at 2:25 pm
How do you mean with "change your join to an in-statement with a sub-query".
Can you please give me a sample.
// Daniel
November 9, 2009 at 3:23 pm
I have searched on google, but didn't find anything.
I would a preciate if someone can give me a sample, or redirect me to a page.
BR
Daniel
November 10, 2009 at 6:51 am
Would look like this:
select ...
from Companies
where CompanyID in
(select CompanyID
from Addresses
where Address1 like '%' + @Variable + '%'
or Address2 like '%' + @Variable + '%'
or ...)
You'd have to replace that with real code, but that's the general idea.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 10, 2009 at 5:40 pm
Thanks for your help, but I got it to work with JOINS.
Now I need help with optimizing it, it takes to long time to recieve all data.
// Daniel
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply