September 20, 2002 at 5:43 pm
I need a way to use a multiple select in a search. The multiple choice is not the search itself, it's a part of the search and includes Categories for Advertisers. How can I use make the search include Advertisers which have the selected categories? Here's are the example table layouts:
table_Advertisers
IDName ...
-------------
1xxx ..
2yyy ..
table_Categories
IDDescription
---------------------
1aaa
2bbb
3ccc
4ddd
table_AdvertiserCategories
AdvertiserIDCategoryID
---------------------------------
11
12
21
23
24
Edited by - kensai on 09/20/2002 5:46:28 PM
September 21, 2002 at 2:12 pm
Not sure what you exactly want. There are a lot of threads around here to build some kind of search engine.
If your problem is linking the advertiser to the category, you can use a join as follows :
SELECT a.* from table_Advertisers a INNER JOIN table_AdvertiserCategories ac ON a.ID = ac.AdvertiserID INNER JOIN table_Categories c ON ac.CategoryID = c.ID
WHERE c.Description = 'your search criteria here'
September 21, 2002 at 3:52 pm
quote:
Not sure what you exactly want.
What I need is this: The categories will be listed as checkboxes in the search form, like "Category 1, Category 2" etc. For example the user can select 1 and 2, which means "find me the advertisers who has category 1 and 2 available". Looking to the table table_AdvertiserCategories this query should return Advertiser ID "1" as result. How can I do this?
September 22, 2002 at 4:56 pm
Maybe something like this pseudo code will work for you
Declare @search_clause char(1000)
set @search_clause = ''
if Category = 1
set @search_clause = CategoryID = 1
if Category = 3
begin
if @search_clause = ''
set @search_clause = 'CategoyID = 3
else
set @search_clause = @search_clause + 'or CategoryID = 2"
end
if Category = 4begin
if @search_clause = ''
set @search_clause = 'CategoyID = 4
else
set @search_clause = @search_clause + 'or CategoryID = 4"
end
declare @cmd
set @cmd = 'select AdvertiserID from table_AdvertiserCategories where ' + @search _clause
exec (@CMD)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 23, 2002 at 5:32 am
This will reutrn the required results. As to how to properly perform for your app, depends on your implementation.
SELECT a.*
from table_Advertisers a
INNER JOIN table_AdvertiserCategories ac
ON a.ID = ac.AdvertiserID
WHERE ac.CategoryID IN (1,2)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 23, 2002 at 7:10 am
Thanks for the all your help. I've found the solution but I need some dynamic SQL help for it. The code I've found is working itself alone but when I implement it in the dynamic SQL code it gives me an error. I don't know much about dynamic SQL and I do hope that you can help me with this error.
Here's how I execute the proc:
exec proc_Search ...,'1,2',...
Here's the error:
Must declare the variable '@availability'.
(given multiple times)
Here's the procedure:(I removed some parts, it was too long)
create procedure proc_Search
...,@availability varchar(3000),....
as
set nocount on
declare @sql nvarchar(3000),....,@sqlAvailability varchar(3000),@sqlAvailabilityStart varchar(3000),...
set @sql=''
...
set @sqlAvailability=' '
set @sqlAvailabilityStart=' '
...
...
if @availability<>'all'
begin
set @ssqlAvailabilityStart='declare @IDListPositionAvail int
declare @ArrValueAvail varchar(2000)
declare @TableVarAvail table ( EmployeeID varchar(50) NOT NULL )
set @availability = COALESCE(@availability ,'''')
IF @availability <> ''''
BEGIN
set @availability = @availability + '',''
while patindex(''%,%'' , @availability ) <> 0
begin
select @IDListPositionAvail = patindex(''%,%'' , @availability)
select @ArrValueAvail = left(@availability, @IDListPositionAvail - 1)
INSERT INTO @TableVarAvail (EmployeeID) VALUES (@ArrValueAvail)
select @availability = stuff(@availability, 1, @IDListPositionAvail, '''')
END
END '(<==there is a [ /red ] here, so this is the end of @sqlAvailabilityStart :] )
set @sqlAvailability=' and ID in
(select AdvertiserID from dbo.[AdvertiserAvailabilities] where AvailabilityID in
(SELECT EmployeeID FROM @TableVarAvail)
) '
end
set @sql=
@sqlAvailabilityStart+
'select ID,FullName,City,State,Picture1
from dbo.[Advertisers]
where Gender='+convert(varchar,@gender)+' '
..
+@sqlAvailability
...
+' and Approved=1'
exec dbo.sp_executesql @sql
go
Edited by - kensai on 09/23/2002 12:43:18 PM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply