Solved

sql query

Posted on 2011-09-05
20
294 Views
Last Modified: 2012-08-14
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?
0
Comment
Question by:JCWEBHOST
  • 9
  • 5
  • 5
  • +1
20 Comments
 
LVL 19

Expert Comment

by:Limbeck
ID: 36482804
how about

select something
from your table
where foodtype like '%<<your checkboxvalue>>%'
0
 
LVL 19

Expert Comment

by:Limbeck
ID: 36482816
tbh  why not have 3 tables, foodtypes, restaurants and foodtypesatrestaurant f.i. ,
0
 

Author Comment

by:JCWEBHOST
ID: 36482817
will it check if a have Italian,Portuguese input form my checklistbox

and this is stored in my coloum Buffet,Chinese,Indian,Italian
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:JCWEBHOST
ID: 36482820
can not have 3 tables, i have a table to pull the list of foodtypes
0
 

Author Comment

by:JCWEBHOST
ID: 36482827
here my tables

TABLE: Food Types
COLOUMS : id,foodtypes

TABLE Restaurants
COLOUMS : id,restaurants,foodtypes
0
 
LVL 19

Expert Comment

by:Limbeck
ID: 36482828
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
 
LVL 19

Expert Comment

by:Limbeck
ID: 36482832

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
 

Author Comment

by:JCWEBHOST
ID: 36483139
i do not want to put a 3 table
0
 
LVL 19

Expert Comment

by:Limbeck
ID: 36483143
then use a loop in your asp.net code and do 1 value at a time
0
 

Author Comment

by:JCWEBHOST
ID: 36483154
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 36483161
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
 

Author Comment

by:JCWEBHOST
ID: 36483180
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 36483205
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
 

Author Comment

by:JCWEBHOST
ID: 36483244
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
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 500 total points
ID: 36483268
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 36483273
Sorry the beginning of the first line should be

select distinct name

instead of

select distinct rest
0
 

Author Comment

by:JCWEBHOST
ID: 36483293
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
 

Author Comment

by:JCWEBHOST
ID: 36483297
should i put an empty space by the coma?

like  'Buffet, Indian'
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 36483300
Sorry, my fault. Should be 'BUFFET','INDIAN'

Otherwise its searching for "BUFFET,INDIAN" as an entire string.
0
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 36483344
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

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question