[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

sql query

Posted on 2011-09-05
20
Medium Priority
?
303 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

650 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