help with multiple databases

[gcapp]

Ok this may sound complicated but hopefully someone can help me.

First if you go to this link:
Restaurants (http://www.enchantedmountains.org/asp/citysearch.asp?Category=1300)
and choose a city you will get a listing of restaurants. Note that in each individual listing the city name is displayed.

Now go to this link:
Outdoor Fun (http://www.enchantedmountains.org/asp/outdoor_fun.asp?Parent=1200)
and choose Golf. There you will see a listing of golf courses, but notice that the city name is not listed, it's a CityID number.

Now i know why this is happeneing but i don't know how to correct it.

I have two tables that are involved here that have JOINS. They are as follows:

Businesslistings.Category JOINED to Category.CategoryID
and
Businesslistings.City JOINED to City.CityID

The "outdoor_fun.asp" page needs to read the JOIN and pull the city name from the City table.

Now I have attached all three asp pages that are involved with these two result pages that you viewed.

"citysearch.asp" and "searchresults.asp" are for the restaurant results and those pages are fine. I put them here so you could see the SQL.

"outdoor_fun.asp" is the page where I need the SQL slightly altered so when you view the golf listings (or any other category), the city name will show. I need it where it will pull in the city name from the City table.

I hope someone can help me with this. And I hope I made sense here.

Thanks,
Gary

[Morgoth]

I see what you mean.

---
Burger King
Ames Plaza, Rt. 16
Yorkshire, New York 14173
5927028
---

---
Amish Country Tours
12096 Northeast Road
6, New York
Phone: (716) 287-3941
---

There is a city listed for the stores and such, but there is no city listed for the tours and such.

Are you really using two differnt databases or are you just using two differnt tables?

I get home soon, and I'll check out the code when I get home. But I understand exactly what you want.

[gcapp]

Morgoth,
Holy, I didn't think you would respond to this post too!

Yes I meant two tables I am using not databases. And you can plainly see my problem.

If there is a simple answer that woudl be great.

Man after all this I'll have to repay you!!'

Gary

[Morgoth]

No need for anything, I get my happiness from doing a good job :)

[gcapp]

Morgoth,
I really appreciate the help.

About the multiple table problem, I really would like the pages to stay the same. The reason I have a drop-down list for restaurants, shopping, etc is because those lists are so very long. With the Outdoor Fun, there are only like 8 categories and I would like to leave them listed before people go deeper.

If there is a simple sql solution that would be great.

Gary

[Morgoth]

I didn't want to put that, it was an acciendent, I knew you had a reason.


I was gunna replace it with something that was also wrong..

I gotta slow down..

:(


Another edit:
What about your field names? Are you absolutely sure they are right?

[Morgoth]

Inside outdoor_fun you have rsMembers("City"), are you not taking the cities from other table? I think this is wrong, and it isn't showing up cause it isn't there.

You either need to open up the table of the cities or you need to get the index number or the cities

[gcapp]

Well I believe the rsMembers("City") pulls the city name from the City table because of the JOIN. But maybe I am wrong. Now that I think about it, I had this script before I did the restaurant thing. When I used this script, the Businesslistings table had the actual name of the city in the field, thus it pulled a name to rsMembers("City"). Then I changed the database and had the city field changed to a number and had it linked to the City table because of the restaurant script.

So I believe you are right. So how would I change that?? I don't want to mess up the code and have it not work at all.

Gary

[Morgoth]

Ok, let's try something.

I have never used the inner join thing, so It's new to me, but if we try the same thing with this sql statment in outdoor_fun we will get it.

I am looking for the right statment to pick.

[Morgoth]

This is very difficult because I don't have the db to look at.

You need to find the index number of the city each THING is in.

So when you removed the city field, you had to add the city_ID field which will have the same number as ID number for each city in the City table.

Do you follow?

If Cherrydowns Golf course had city=Kingstion in the members table, then the new city_id, you added when you removed the field city, should equal the id number of Kingston in the table called city.

Now you follow?

Now you must either send me your database or maybe easier and a better idea, database screen shots. I might need screen shots if you don't understand what I mean. Well, either screenshots or a list of the Fields that are in the members table, and the city table.

[gcapp]

Ok, here is the breakdown of the three tables involved in this.

Database is a called Businesses

Table: Businesslistings
Fields: BusinessID [number]
Image_ID [text]
Category [number]
Business [text]
Address [text]
City [number]
State [text]
Zip [text]
Phone [text]
Phone2 [text]
Fax [text]
Website [text]
Email [text]
Information [text]

Table: Category
Fields: CategoryID [number]
Categorylabel [text]

Table: City
Fields: CityID [number]
Citylabel [text]
Cityzip [text]

I joined Businesslistings.Category to Category.CategoryID
And Businesslistings.City to City.CityID



I hope this is what your are after. I get the jist of what you're saying, but it's like everything else I am trying do, I know in my head what to do - but getting code on a piece of papaer is another.

Gary

[Morgoth]

Ok, so it's connected...

City [number] should be set to the city the tour is in. Just make sure it is.


Now, this is a little weird, cause I have no clue if you connected the tables together.

Cause there are no error messages to check, and it seems like it should work!

[Morgoth]

I think the problem is with the db, it's not connected correctly, you have ot make sure of it, cause I think the code is right.

[gcapp]

Morgoth,
Well a new day - hopefully I can get the two problems I have solved today.
As far the two table deal, the database is setup right.
You see in the outdoor_fun.asp page, down where there is an sql line for each recordset to be displayed? This line:

sqlMembers = "SELECT * FROM " & MEMBER_TABLE & " WHERE Category LIKE '%" & sParent & "%' ORDER BY Business"


Well this sql doesn't tap into the City table - that is why it only shows the number from that field. It's like saying, "ok, for record number one in the Businesslistings table, read me what is listed there." And if you did, you would read a number in the City field. The sql doesn't have anything in there about the JOIN between the Businesslistings tabel and the City table.

That is where i am stuck. This page works correctly, but I just need to dabble with the sql to see if it can pull the city form the city table, not the Businesslistings table.

If you think of anything, let me know.

Gary

[Morgoth]

Can you explain to me what WHERE Category LIKE '%" & sParent & "%' is? And Does?

Now this is what I think we should do.

Inside of the Members table, there is the field city which holds the city table id number for a city.

Now I don't have the code as a referance or something, but if the code i pasted above doesn't do what I think it might be doing, I want you to ADD this code, and chnage the place where city is, to something else.


SQL = "SELECT thecityfield FROM thetablecity WHERE ID=( & string_for_city_id_in_members_table & ") & " "

execute that, then go something like RS.City

I don't have the code here for a refferance, but that will take the ID number listed in the members table unver the city field and look up the same number in the city table in the cityname field.

Try it, I think you know what I ma trying to do.