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?
JCWEBHOSTAsked:
Who is Participating?
 
EvilPostItCommented:
select distinct rest from pretoria p
join foodtypes f on p.foodtypes like '%'+ f.type+'%'
where f.type IN ("SEARCH VALUES") 

Open in new window


The "SEARCH VALUES" sections should have something like 'BUFFET,CHINESE' etc.
0
 
LimbeckCommented:
how about

select something
from your table
where foodtype like '%<<your checkboxvalue>>%'
0
 
LimbeckCommented:
tbh  why not have 3 tables, foodtypes, restaurants and foodtypesatrestaurant f.i. ,
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
JCWEBHOSTAuthor Commented:
will it check if a have Italian,Portuguese input form my checklistbox

and this is stored in my coloum Buffet,Chinese,Indian,Italian
0
 
JCWEBHOSTAuthor Commented:
can not have 3 tables, i have a table to pull the list of foodtypes
0
 
JCWEBHOSTAuthor Commented:
here my tables

TABLE: Food Types
COLOUMS : id,foodtypes

TABLE Restaurants
COLOUMS : id,restaurants,foodtypes
0
 
LimbeckCommented:
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
0
 
LimbeckCommented:

TABLE: Food Types
COLOUMS : id,foodtypes

TABLE Restaurants
COLOUMS : id,restaurants

TABLE FoodTypesAtRestaurant
COLUMNS: id, restaurant_id,foodtype_id

cant change it to this?
0
 
JCWEBHOSTAuthor Commented:
i do not want to put a 3 table
0
 
LimbeckCommented:
then use a loop in your asp.net code and do 1 value at a time
0
 
JCWEBHOSTAuthor Commented:
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.
0
 
EvilPostItCommented:
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.

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%' END

Open in new window

0
 
JCWEBHOSTAuthor Commented:
i check that out and it works, but my problem is here

select @Buffet=1,@Italian=0,@Chinese=0

my foodtypes are in the table, how would i know how to assign that?
0
 
EvilPostItCommented:
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")

Open in new window

0
 
JCWEBHOSTAuthor Commented:
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



0
 
EvilPostItCommented:
Sorry the beginning of the first line should be

select distinct name

instead of

select distinct rest
0
 
JCWEBHOSTAuthor Commented:
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
0
 
JCWEBHOSTAuthor Commented:
should i put an empty space by the coma?

like  'Buffet, Indian'
0
 
EvilPostItCommented:
Sorry, my fault. Should be 'BUFFET','INDIAN'

Otherwise its searching for "BUFFET,INDIAN" as an entire string.
0
 
OnALearningCurveCommented:
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.
<%
'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)
%>

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.