Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-13 : 08:07:54
|
| anthony writes "I am working on a a criteria search form but having a little bit of trouble with the where statements.what I am trying to do is filter out results where a criteria is true in one table and another criteria is true in another table such as I want to look for all the restaurants that are located in reno and serves thai food.this is sort of the way the query string looks like, the where part is wrong but by entering it in I was hoping you would get an idea of what I was trying to do. SELECT Name, FROM Restaurant, ByArea, Cities, ByFood, Cuisines WHERE (Restaurant.NameID=ByArea.NameID AND ByArea.CityID=Cities.CityID AND Cities.City='reno') AND (Directory.NameID=ByFood.NameID AND ByFood.CuisineID=Cuisines.CuisineID AND Cuisine ='thai');" |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-13 : 08:30:37
|
| The directory table referenced in your where clause does not match a table in your from clause . . .<O> |
 |
|
|
alantis13
Starting Member
2 Posts |
Posted - 2002-06-13 : 15:17:30
|
| oops Directory.ID should be Restaurant.IDthe error message I get says to few paremeters expect 2I looked at the "implementing a dynamic where clause" article but it queries from only on table and I need two.Should I be using something else, like union or subquery?or query reno first, put it in a temporary table, then query thai from that temporary table? |
|
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2002-06-13 : 17:00:15
|
| maybe your are getting an error becuase there is a ',' after your Name field and you are not specifiying any other fields.Got SQL? |
|
|
|
alantis13
Starting Member
2 Posts |
Posted - 2002-06-14 : 04:03:18
|
| the "," is in there because I had another column (hyperlink) I wanted to display, but I removed that from the question and forgot to proof read my post. The sql query string is correct on my end, just need to figure out how to query both tables and have the results true to both criterias. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2002-06-14 : 04:28:36
|
| drop table Restaurant drop table ByArea drop table Cities drop table ByFood drop table Cuisinecreate table Restaurant (NameID int, [Name] char(5))create table ByArea (NameID int, CityID int, [Name] char(5))create table Cities (CityID int, City char(5))create table ByFood (NameID int, CuisineID int, [Name] char(5))create table Cuisine (CuisineID int, Cuisine char(5))insert into Restaurant values(1,'arest')insert into Restaurant values(2,'test')insert into Restaurant values(3,'puke')insert into ByArea values(1,1,'')insert into ByArea values(2,2,'')insert into ByArea values(3,3,'')insert into Cities values(1,'reno')insert into Cities values(2,'test')insert into Cities values(3,'reno')insert into ByFood values(1,1,'ugly')insert into ByFood values(2,2,'bad')insert into ByFood values(3,3,'good')insert into Cuisine values(1,'thai')insert into Cuisine values(2,'meat')insert into Cuisine values(3,'fish')SELECT Restaurant.[Name] FROM Restaurant INNER JOIN ByArea ON Restaurant.NameID = ByArea.NameID INNER JOIN Cities ON ByArea.CityID = Cities.CityID INNER JOIN ByFood ON Restaurant.NameID = ByFood.NameID INNER JOIN Cuisines ON ByFood.CuisineID = Cuisines.CuisineIDWHERE Cities.City= 'reno' AND Cuisines.Cuisine = 'thai'This doesn't give me a problem, I get the expected result...Hope it helps..PeaceRick |
|
|
|
|
|
|
|
|
where statement