sql with three tables

Results 1 to 2 of 2

Thread: sql with three tables

  1. #1
    Join Date
    Dec 1969

    Default sql with three tables

    I hope someone can help me I am a rookie to asp.<BR><BR>Ok, let me explain what I&#039;ve got.<BR><BR>I have three tables that are JOINED.<BR>One table is called Businesslistings, one called Category. and one called City.<BR><BR>The JOINS on these tables are as follows:<BR>Businesslistings.Category is joined to Category.CategoryID and<BR>Businesslistings.City to City.CityID<BR><BR>Now if you go to this url:<BR>http://www.enchantedmountains.org/asp/citysearch.asp?Category=1300<BR>If you pick a city, you will get a listing of restaurants for that city. Notice that the City is listed by it&#039;s name.<BR><BR>This page has sql statements from two pages - the citysearch page and the results page. The sql&#039;s for each page are as follows:<BR><BR>citysearch - sql="SELECT DISTINCTROW [City].[CityID], [City].[Citylabel], [Businesslistings].[Category], [Businesslistings].[City], Count(*) AS [Count Of City], [Businesslistings].[Category] FROM City INNER JOIN Businesslistings ON [City].[CityID]=[Businesslistings].[City] GROUP BY [City].[CityID], [City].[Citylabel], [Businesslistings].[Category], [Businesslistings].[City] HAVING (((Businesslistings.Category)=acat)) ORDER BY Count(*) desc" <BR>sql=replace(sql,"acat",request.querystring("ca tegory"))<BR><BR>and for the results page - sql="SELECT Businesslistings.BusinessID, Category.Categorylabel, City.Citylabel, Businesslistings.Business, Businesslistings.Address, Businesslistings.State, City.Cityzip, Businesslistings.Phone, Businesslistings.Phone2, Businesslistings.Fax, Businesslistings.Website, Businesslistings.Email FROM City INNER JOIN (Category INNER JOIN Businesslistings ON Category.CategoryID = Businesslistings.Category) ON City.CityID = Businesslistings.City WHERE category=acatID AND city=acitID ORDER BY Business asc" <BR>sql=replace(sql,"acatID",request.form("categor y"))<BR>sql=replace(sql,"acitID",request.form("cit y"))<BR><BR><BR>Now if go to this url: http://www.enchantedmountains.org/asp/outdoor_fun.asp?Parent=1202<BR>you will see a listing of golf courses, but notice that the City is a number.<BR><BR>Now this golf page uses this SQL:<BR>sqlMembers = "SELECT * FROM Businesslistings WHERE Category LIKE &#039;%" & sParent & "%&#039; ORDER BY Business" to show the results.<BR><BR>What I need is for the sql from the golf page to include everything it does now, except I need it to tap into the City table as well, so it will display the city name, not the CityID.<BR><BR>In my Businesslistings table the City field is a number, which is joined to the City table field CityID.<BR><BR>The datatypes of the particular fields are:<BR>Table:Businesslistings<BR>Field:City [number]<BR><BR>Table:City<BR>Fields:CityID[number], Citylabel[text], Cityzip[text]<BR><BR>So if someone can simply make the golf sql pull in the city name from the city table, along with everything else its getting from the Businesslistings table, it would really help me out.<BR><BR>If anyone needs anymore info, please let me know.<BR><BR>Gary

  2. #2
    Join Date
    Dec 1969

    Default Since it's an Access database..

    .. just use the Query Builder from within MS Access and let it create your query. It&#039;s a lot easier than mucky with the stupid Access join syntax.<BR><BR>Oh, yeah. Sql Injection is not your friend. Read this article:<BR>http://www.4guysfromrolla.com/webtech/061902-1.shtml<BR><BR>And then, click this link:<BR>http://www.enchantedmountains.org/asp/outdoor_fun.asp?Parent=120&#039;2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts