Please Help With Query, Please don't Laugh

[Guest_imported]

To anyone who can help,
I'm a complete newbie to sql. I'm using an access database and active server pages for my new web site. The site is an online apartment search and I have two tables, one with the different apartment complex information, and one with every different floorplan of the apartments. I've been doing the search on a per unit basis. The problem is that if the search is broad then many units will show up from the same complex which seems a little redundant. I'd like to have each complex show up individually and then the user can click on a detail page for more information. I'd like the query to show the low price and high price for every unit at that particular complex that would meet the search criteria. Here is the code that I'm using below. Thanks in advance.







SELECT Apartment_Info.Apartment_ID, Apartment_Info.Apartment_Name, Apartment_Info.Address, Apartment_Info.City, Apartment_Info.State, Apartment_Info.Zip, Apartment_Info.Area, Apartment_Info.Apartments_Image, Apartment_Info.Location, Apartment_Info.YearBuilt, Apartment_Info.Number_of_Units, Apartment_Info.Management, Apartment_Info.Hrs, Apartment_Info.District, Apartment_Info.Elementary_School, Apartment_Info.Middle_School, Apartment_Info.High_School, Apartment_Info.Access_Gates, Apartment_Info.Alarms, Apartment_Info.Pet, Apartment_Info.Large_Pet, Apartment_Info.Pet_Comments, Apartment_Info.Application_Fee, Apartment_Info.Lease_Periods, Apartment_Info.Corporate_Units, Apartment_Info.Short_Term_Lease, Apartment_Info.Covered_Parking, Apartment_Info.Garage, Apartment_Info.Pools, Apartment_Info.Pool_Qnty, Apartment_Info.Laundry, Apartment_Info.Tennis, Apartment_Info.Volleyball, Apartment_Info.Water_Volleyball, Apartment_Info.Fitness_Center, Apartment_Info.Jogging_Trail, Apartment_Info.Raquetball, Apartment_Info.Basketball, Apartment_Info.Clubroom, Apartment_Info.Jacuzzi, Apartment_Info.Specials, Apartment_Info.Phone_Number, Apartment_Info.Comments, Apartment_Info.Sauna, Apartment_Info.Business_Center, Apartment_Info.View, Unit_Info.Unit_ID, Unit_Info.Plan, Unit_Info.Description, Unit_Info.Type, Unit_Info.SqFt, Unit_Info.Low_Price, Unit_Info.High_Price, Unit_Info.Deposit, Unit_Info.Fireplace, Unit_Info.Washer_Dryer, Unit_Info.Conn, Unit_Info.Prov, Unit_Info.Icemaker, Unit_Info.Microwave, Unit_Infutside_Storage, Unit_Info.Loft, Unit_Info.Townhome, Unit_Info.Study, Unit_Info.Sunroom, Unit_Info.Den, Unit_Info.Garden_Tub, Unit_Info.Walk_in_Closet, Unit_Info.Vaulted_Ceilings, Unit_Info.Linen_Closet, Unit_Info.Floorplan_Path, Unit_Info.Patio_Balconet
FROM Apartment_Info INNER JOIN Unit_Info ON Apartment_Info.Apartment_ID = Unit_Info.Apartment_ID
WHERE Apartment_Name LIKE 'varName' AND Type LIKE 'varType' AND Area LIKE 'varArea' AND Pools LIKE 'varPools' AND Washer_Dryer LIKE 'varWasher_Dryer' AND District LIKE 'varDistrict' AND Access_Gates LIKE 'varAccess_Gates' AND Jacuzzi LIKE 'varJacuzzi' AND Microwave LIKE 'varMicrowave' AND Fireplace LIKE 'varFireplace' AND Pet LIKE 'varPet' AND Large_Pet LIKE 'varLarge_Pet' AND Fitness_Center LIKE 'varFitness_Center' AND Covered_Parking LIKE 'varCovered_Parking' AND Garage LIKE 'varGarage' AND Zip Like 'varZip' AND Conn LIKE 'varConn' AND Prov LIKE 'varProv' AND Elementary_School LIKE 'varElem' AND Middle_School LIKE 'varMid' AND High_School LIKE 'varHigh' AND Basketball LIKE 'varBasketball' AND Alarms LIKE 'varAlarms' AND Water_Volleyball LIKE 'varW_Volleyball' AND Tennis LIKE 'varTennis' AND Vaulted_Ceilings LIKE 'varVaulted_Ceilings' AND Jogging_Trail LIKE 'varJogging_Trail' AND Sauna LIKE 'varSauna' AND Raquetball LIKE 'varRaquetball' AND Garden_Tub LIKE 'varGarden_Tub' AND Volleyball LIKE 'varVolleyball' AND Clubroom LIKE 'varClubroom' AND Business_Center LIKE 'varBusiness_Center' AND Attached_Garage LIKE 'varAttached_Garage' AND View LIKE 'varView' AND Laundry LIKE 'varLaundry' AND Patio_Balconet LIKE 'varPatio' AND Low_Price >varLow_Price AND High_Price < varHigh_Price
ORDER BY Apartment_Name, SqFt

 

[carp]

I'm not real familiar with Access, but I think the DISTINCT operator may be what you're looking for:

SELECT DISTINCT Apartment_Info.Apartment_ID,
Apartment_Info.Apartment_Name, Apartment_Info.Address,
Apartment_Info.City, Apartment_Info.State, Apartment_Info.Zip,
Apartment_Info.Area, Apartment_Info.Apartments_Image,
Apartment_Info.Location, Apartment_Info.YearBuilt,
Apartment_Info.Number_of_Units, Apartment_Info.Management,
Apartment_Info.Hrs, Apartment_Info.District,
Apartment_Info.Elementary_School, Apartment_Info.Middle_School,
Apartment_Info.High_School, Apartment_Info.Access_Gates,...

 

[Guest_imported]

I tried the DISTINCT operator with no success, but thanks for the suggestion.

 

[rac2]

Maybe do the search in two steps, first show the apartment complexes with units that match the criteria. Then do a second query limited to the complex that is selected and show all of the units in that complex.

The first stage can use a GROUP BY statement to get the MIN() AND MAX() prices for each complex.

Use the id of the selected complex in the WHERE clause of the second query.

 

[Guest_imported]

I'm such a newbie to SQl, That approach sounds great could you Please give me an axample on how that might be implemented. Thanks very much.

 

[rac2]

I can suggest some queries that you can use to retrieve data into several recordsets and leave the ASP for you. (Since this is the SQL Forum and that is the fun part.)


In Access design a Summary Query to find high and low values for the units in each complex. Name it Apartment_Ranges. The SQL would be -

SELECT Apartment_ID, MIN(SqFt) AS "Smallest", MAX(SqFt) AS "Largest", MIN(Low_Price) AS "Economy", MAX(High_Price) AS "Luxury" 
FROM Unit_Info
GROUP BY Apartment_ID

You may need to adjust this, as I don't know what data is in the price columns, how can a unit have more than one price?



This query lists key information about apartment complexes that match the search criteria. I omit the details of the WHERE clause for clarity. You have that in your post. Use this query to generate an ASP recordset to display the results of the first stage.

SELECT Apartment_Info.Apartment_ID, Apartment_Info.Apartment_Name, Apartment_Info.City,  Apartment_Ranges.Smallest, Apartment_Ranges.Largest, Apartment_Ranges.EconomyPrice, Apartment_Ranges.LuxuryPrice
FROM Apartment_Info 
JOIN Apartment_Ranges ON Apartment_Info.Apartment_ID = Apartment_Ranges.Apartment_ID

WHERE Apartment_Info.Apartment_Name  LIKE 'varName' etc.

Build links in your results page that contain the Apartment_ID in the querystring, e.g.

vAptID = rsFindComplexes(&quot;Apartment_ID&quot;

<a href=&quot;showUnitsInComplex.asp?complex=<%= vAptID %>&quot; >

This will pass the apartment ID to the ASP script that builds the display of units within one complex. That data will be available like this -
vAptID = Request.Querystring(&quot;complex&quot;


Now back to SQL. This query will get the list of units in the selected complex, show whatever detail you have.
This is javascript code to build the query.

cmdShowUnits.CommandText = "SELECT * FROM UnitInfo
WHERE Apartment_ID = " + vAptID;
rsShowUnits = cmdShowUnits.Execute(qShowUnits);

 

[Guest_imported]

I've been trying to implement your suggestions with no luck. I'm not sure how I can combine the two queries. First I have the first query that searches the database on a per unit basis. Next I need to run the results of that query through the one that you suggested to show the range of price and square footage per apartment complex. I tried to do this using a subquery but the problem is I'm not sure what I'm doing. I very much appreciate your help.

Thanks,

A. Davis

 

[rac2]

Were you able to create the Summary Query named Apartment_Ranges? If so then it is always available for use in other queries. It is called a VIEW. It is just like a table that you can get data from.

I have assumed that we are starting with a user who is specifiying some of the features of a place to live. First we will show them the names of apartment complexes that meet their requirements. This first search uses only features in the Apartment_Info table to select apartment complexes. We probably will not explicitly display those features in our response, other that to say &quot;All of the complexes listed below meet your criteria.&quot; We will show information about location, spaciousness, and price. From this, the person will select one apartment complex for detailed information.

Does that make sense?

 

[Guest_imported]

I really appreciate your help and it does make sense. All the prices are in the Unit_Info table. When the user performs a search he(she) puts in the low and high price range desired and also the number of bedrooms needed. In the summary query I think the price range is being derived on a per complex basis including 1,2,3 bedrooms. What I was hoping to do is this. The user performs a search based on the criteria they type in, it may be a one bedroom or it may be all floorplans. Each would have a different Min low price and a Max high price. Also a different Min size and a Max size. So if the user puts in one bedrooms under a $1000.00 for example, the search might return several one bedrooms from the same complex. I was hoping to show the ranges based on that search if possible while passing the unit ids to the detail page that fit the criteria.
Again I really appreciate your help.

A.Davis

 

[rac2]

OK, scratch all that went before.

We could do this in three web pages generated from three ASP scripts.

First, a page with a form to enter criteria, call it selectAHome.asp. It captures all those varThis and varThat values and submits them to the second page.

Second, a page that queries the database for apartment complexes that have units that match the criteria, call it showComplexes.asp.

The query will get the name of each apartment complex that has units with the desired price range and floorplan.
An apartment complex will only be listed one time together with the lowest Low_Price, the highest High_Price, and the number of units in the complex that matched the criteria.

No data about a particular unit will be shown.

The GROUP BY clause does two things, it allows summary calculations and it shows each Apartment_Name once.

SELECT Apartment_Info.Apartment_Name,
   MIN(Unit_Info.Low_Price) AS "Economy",
   MAX(Unit_Info.High_Price) AS "Luxury",
   COUNT(*) AS "Number of Units Found"

FROM Apartment_Info
INNER JOIN Unit_Info ON Apartment_Info.Apartment_ID = Unit_Info.Apartment_ID

WHERE Unit_Info.Floorplan_Path = varFloorplan
  AND Unit_Info.Low_Price > varLow_Price
  AND Unit_Info.High_Price < varHigh_Price

GROUP BY Apartment_Info.Apartment_Name

Still in the second page, the script next builds the display from the recordset generated from this query. Build links around each complex name with a querystring that includes the Apartment_name. Also save all those criteria values in Session variables. The link is to the third page. It would look like this

showUnits.asp?complex=SugarPineCabins

Third, a page that queries the database for units in the one apartment complex that meet the criteria, call it showUnits.asp.

This page uses a query much like your gigantomongos query to get all the details about the units in the single complex that match the criteria. The criteria values are available in Session variables and the name of the complex is in Request.Querystring(&quot;complex&quot. Be sure to specifiy the complex name in the WHERE clause.

 . . .
WHERE Apartment_Name  LIKE 'SugarPineCabins' 
   AND . . .

Use the recordset generated from this query to build the display. Note that we did not pass the unit ids from one page to another, instead we ran another query to get them.

 

[Guest_imported]

Sorry to keep bugging you but I'm a little lost. I understand the Group By statements but here is the problem. On the initial search page the user types all of the search criteria. low price, high price, vaulted ceilings, washer dryer connections, views, zip code, school district etc. On the second page, results.asp the query is performed on a per unit basis joining the two tables, Unit_Info and Apartment_Info. A lot of the ammenities are particular to the unit such as washer and dryer, fireplace and so on. The Select statement joins the two tables and searches all the fields against what the user types in. The way it is now Every unit that matches the criteria is shown on the results page along with the apartment name and all amenities. Can I group the units by complex at that point? Showing Type, Apartment complex, Amenites that are particular with that Group. The user then clicks on the Complex name which will take them to the detail page. On the detail page I'd like only the units that meet the criteria to be shown. They could click on the units to show them floorplans and other information.

 

[rac2]

&quot;The way it is now Every unit that matches the criteria is shown on the results page &quot;

You control what is shown on the results page with the ASP script, not the SQL.




&quot;Can I group the units by complex at that point? Showing Type, Apartment complex, Amenites that are particular with that Group.&quot;

Grouping has a couple of different meanings here. One sense is that detail rows are sorted so that rows with the same value in a column are all listed together. In SQL this is accomplished with the ORDER BY clause. The other sense is that only one row is listed for each value along with some summary data such as how many detail records have that value. In SQL this is accomplished with the GROUP BY clause.

I can't think of any other way to help. Maybe someone else will give it a go.

Good luck on your project.

 

[Guest_imported]

I got the sql to work but when I test the site I get this Error Message

Microsoft VBScript compilation error '800a0401'

Expected end of statement

This Error came after I changed the SQL. It worked fine before. ?




SELECT Apartment_Info.Apartment_Name,Apartment_Info.Area,MIN(Unit_Info.Low_Price) AS &quot;Economy&quot;,MAX(Unit_Info.High_Price) AS &quot;Luxury&quot;, COUNT(*) AS &quot;Number of Units Found&quot;
FROM Apartment_Info INNER JOIN Unit_Info ON Apartment_Info.Apartment_ID = Unit_Info.Apartment_ID
WHERE Apartment_Name LIKE 'varName' AND Type LIKE 'varType' AND Area LIKE 'varArea' AND Pools LIKE 'varPools' AND Washer_Dryer LIKE 'varWasher_Dryer' AND District LIKE 'varDistrict' AND Access_Gates LIKE 'varAccess_Gates' AND Jacuzzi LIKE 'varJacuzzi' AND Microwave LIKE 'varMicrowave' AND Fireplace LIKE 'varFireplace' AND Pet LIKE 'varPet' AND Large_Pet LIKE 'varLarge_Pet' AND Fitness_Center LIKE 'varFitness_Center' AND Covered_Parking LIKE 'varCovered_Parking' AND Garage LIKE 'varGarage' AND Zip Like 'varZip' AND Conn LIKE 'varConn' AND Prov LIKE 'varProv' AND Elementary_School LIKE 'varElem' AND Middle_School LIKE 'varMid' AND High_School LIKE 'varHigh' AND Basketball LIKE 'varBasketball' AND Alarms LIKE 'varAlarms' AND Water_Volleyball LIKE 'varW_Volleyball' AND Tennis LIKE 'varTennis' AND Vaulted_Ceilings LIKE 'varVaulted_Ceilings' AND Jogging_Trail LIKE 'varJogging_Trail' AND Sauna LIKE 'varSauna' AND Raquetball LIKE 'varRaquetball' AND Garden_Tub LIKE 'varGarden_Tub' AND Volleyball LIKE 'varVolleyball' AND Clubroom LIKE 'varClubroom' AND Business_Center LIKE 'varBusiness_Center' AND Attached_Garage LIKE 'varAttached_Garage' AND View LIKE 'varView' AND Laundry LIKE 'varLaundry' AND Patio_Balconet LIKE 'varPatio' AND Low_Price >varLow_Price AND High_Price < varHigh_Price GROUP BY Apartment_Info.Apartment_Name,Apartment_Info.Area