JCWEBHOST
asked on
sql query
Hey guys, i have a database and a coloum called foodtypes which stores all my food types for a restaurant.
hey is a sample layout
id Restaurant_Name Food_Types
1 ritz Buffet,Chinese,Indian
now in my website i have a checklistbox which the user select the food types :
here is a list of my checkboxlist
Buffet
Chinese
Indian
Italian
Portuguese
Seafood
Other
now if the user selects one of the following i want to do a search?
the hard part is that my coloum where my foodtypes is stored for a reastarant has "," to seperate the food types :( .
please help?
how would i go about to search in a query?
hey is a sample layout
id Restaurant_Name Food_Types
1 ritz Buffet,Chinese,Indian
now in my website i have a checklistbox which the user select the food types :
here is a list of my checkboxlist
Buffet
Chinese
Indian
Italian
Portuguese
Seafood
Other
now if the user selects one of the following i want to do a search?
the hard part is that my coloum where my foodtypes is stored for a reastarant has "," to seperate the food types :( .
please help?
how would i go about to search in a query?
tbh why not have 3 tables, foodtypes, restaurants and foodtypesatrestaurant f.i. ,
ASKER
will it check if a have Italian,Portuguese input form my checklistbox
and this is stored in my coloum Buffet,Chinese,Indian,Ital
and this is stored in my coloum Buffet,Chinese,Indian,Ital
ASKER
can not have 3 tables, i have a table to pull the list of foodtypes
ASKER
here my tables
TABLE: Food Types
COLOUMS : id,foodtypes
TABLE Restaurants
COLOUMS : id,restaurants,foodtypes
TABLE: Food Types
COLOUMS : id,foodtypes
TABLE Restaurants
COLOUMS : id,restaurants,foodtypes
it will be almost impossible to do what you want :) but what you can do is have a loop in asp.net to use the query i mentioned earlier
TABLE: Food Types
COLOUMS : id,foodtypes
TABLE Restaurants
COLOUMS : id,restaurants
TABLE FoodTypesAtRestaurant
COLUMNS: id, restaurant_id,foodtype_id
cant change it to this?
ASKER
i do not want to put a 3 table
then use a loop in your asp.net code and do 1 value at a time
ASKER
can you show me a loop in sql query to loop through a coloum foodtypes?
if i input Buffet,Chinese,Indian
must loop through the coloum and sperate the string and if it match return the row.
I know it hard but i need to do it that way.
if i input Buffet,Chinese,Indian
must loop through the coloum and sperate the string and if it match return the row.
I know it hard but i need to do it that way.
Something like this should do the trick.
The below searches for only chinese, but obviously you can change the variables in the select statement. I would suggest putting this in an sp and have these variables as inputs.
The below searches for only chinese, but obviously you can change the variables in the select statement. I would suggest putting this in an sp and have these variables as inputs.
declare @FRENCH bit,@ITALIAN bit,@CHINESE bit
select @FRENCH=0,@ITALIAN=0,@CHINESE=1
select * from restaurants
where
foodtype like case @FRENCH WHEN 1 then '%FRENCH%' END OR
foodtype like case @ITALIAN WHEN 1 then '%ITALIAN%' END OR
foodtype like case @CHINESE WHEN 1 then '%CHINESE%' ENDASKER
i check that out and it works, but my problem is here
select @Buffet=1,@Italian=0,@Chin
my foodtypes are in the table, how would i know how to assign that?
select @Buffet=1,@Italian=0,@Chin
my foodtypes are in the table, how would i know how to assign that?
This SHOULD work. The only issue you may have would be when the is a food type IT & ITALIAN. As IT would be a substring of ITALIAN, when selected it would bring up both. If you could have an extra seperator the would help ie [ITALIAN] [IT].
select distinct rest from #restaurants r
join #foodtypes f on r.foodtype like '%'+ f.foodtype+'%'
where f.foodtype IN ("SEARCH VALUES")ASKER
i am lost:
here is my two table that i have:
TABLE ONE: foodtypes
Coloums: id, type
TABLE TWO: pretoria-("Restaurant Name")
Coloums: id, foodtypes,name
here is my two table that i have:
TABLE ONE: foodtypes
Coloums: id, type
TABLE TWO: pretoria-("Restaurant Name")
Coloums: id, foodtypes,name
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry the beginning of the first line should be
select distinct name
instead of
select distinct rest
select distinct name
instead of
select distinct rest
ASKER
select distinct r_name from pretoria p
join foodtypes f on p.foodtypes like '%'+ f.type+'%'
where f.type IN ('Buffet,Indian')
do not give an output
select distinct r_name from pretoria p
join foodtypes f on p.foodtypes like '%'+ f.type+'%'
where f.type IN ('Buffet')
give an output
join foodtypes f on p.foodtypes like '%'+ f.type+'%'
where f.type IN ('Buffet,Indian')
do not give an output
select distinct r_name from pretoria p
join foodtypes f on p.foodtypes like '%'+ f.type+'%'
where f.type IN ('Buffet')
give an output
ASKER
should i put an empty space by the coma?
like 'Buffet, Indian'
like 'Buffet, Indian'
Sorry, my fault. Should be 'BUFFET','INDIAN'
Otherwise its searching for "BUFFET,INDIAN" as an entire string.
Otherwise its searching for "BUFFET,INDIAN" as an entire string.
Hi Guys,
what about trying to turn the selection list into an array and then stepping through the array to build up the SQL statement required to pluck the entries required from the table (see my example code attached)
Apologies if the SAP is bad as my ASP skills are on the poor side of average at the moment (still trying to learn) but you should be able to get the idea and someone with some coding ability may be able to make it work.
Hope this helps,
Mark.
what about trying to turn the selection list into an array and then stepping through the array to build up the SQL statement required to pluck the entries required from the table (see my example code attached)
Apologies if the SAP is bad as my ASP skills are on the poor side of average at the moment (still trying to learn) but you should be able to get the idea and someone with some coding ability may be able to make it work.
Hope this helps,
Mark.
<%
'set up variable foodList and use SPLIT function to turn it into an array of the selected food types
dim foodList
foodList = Split(<<your checkboxvalue>>, ",")
'Set up SQL String
sqlstring = "SELECT * FROM restaurants WHERE "
'Loop through the array and add a where clause for each of the selected food types submitted
for i = 0 to Ubound(foodList)
sqlString = sqlString & "restaraunts.Food_Types LIKE '%"& response.write(foodList(i)) &"%' OR"
next
'Strip the last three characters from the SQL string to remove the additional " OR" that would break the SQL statement
sqlString = LEFT(SqlString,(LEN(sqlString)-3))
'Run the SQL
rs = yourConnection.execute(sqlString)
%>
select something
from your table
where foodtype like '%<<your checkboxvalue>>%'