recordset filtering from another recordset

Report · Sep 18, 2012

okay, here is my problem in a nut shell

i have a search page to display results based on a zip code location(lat/long) coordinates. The first recordset on the page shows the builder information that is within the search parameters:

<%

Dim builder_ID__longValue

builder_ID__longValue = "1"

If (Request.Querystring("long") <> "") Then

builder_ID__longValue = Request.Querystring("long")

End If

%>

<%

Dim builder_ID

Dim builder_ID_cmd

Dim builder_ID_numRows

Set builder_ID_cmd = Server.CreateObject ("ADODB.Command")

builder_ID_cmd.ActiveConnection = MM_home_detective_STRING

builder_ID_cmd.CommandText = "SELECT builder_info_ID, ( 3959 * acos( cos( radians(39.8227054) ) * cos( radians(builder_info.lat ) ) * cos( radians(builder_info.lng) - radians(?)) + sin(radians(39.8227054)) * sin( radians(builder_info.lat)))) AS distance FROM home_detective.builder_info HAVING distance < 75 ORDER BY distance"

builder_ID_cmd.Prepared = true

builder_ID_cmd.Parameters.Append builder_ID_cmd.CreateParameter("param1", 5, 1, -1, builder_ID__longValue) ' adDouble

Set builder_ID = builder_ID_cmd.Execute

builder_ID_numRows = 0

%>

<%

Dim Repeat1__numRows

Dim Repeat1__index

Repeat1__numRows = -1

Repeat1__index = 0

builder_ID_numRows = builder_ID_numRows + Repeat1__numRows

%>

Then from here i have another table that has the builder_ID value listed with all of the school districts that they work within.

What i want to do is to display the search results for the School district *** BUT NOT DISPLAY THE DUPLICATED VALUES *** here is the code i am using

<%

While ((Repeat1__numRows <> 0) AND (NOT builder_ID.EOF))

%>

<%

Dim school_district__MMColParam

school_district__MMColParam = "1"

If (builder_ID.fields.item("builder_info_ID") <> "") Then

school_district__MMColParam = builder_ID.fields.item("builder_info_ID")

End If

%>

<%

Dim school_district

Dim school_district_cmd

Dim school_district_numRows

Set school_district_cmd = Server.CreateObject ("ADODB.Command")

school_district_cmd.ActiveConnection = MM_home_detective_STRING

school_district_cmd.CommandText = "SELECT DISTINCT school_district FROM home_detective.school_district WHERE builder_ID = ?"

school_district_cmd.Prepared = true

school_district_cmd.Parameters.Append school_district_cmd.CreateParameter("param1", 5, 1, -1, school_district__MMColParam) ' adDouble

Set school_district = school_district_cmd.Execute

school_district_numRows = 0

%>

<%=(school_district.Fields.Item("school_district").Value)%>

</div>

<%

Repeat1__index=Repeat1__index+1

Repeat1__numRows=Repeat1__numRows-1

builder_ID.MoveNext()

Wend

%>

as you can see, it displays the results but if 2 builders have the same school district, it displays that school district twice. I only want the DISTINCT values

any ideas?

Report · Sep 18, 2012

You have the answerkey in your post itself.

R&D DISTINCT parameter. That should do the trick.

Report · Sep 18, 2012

unfortunatly that doesn't do the trick. It filters through each line per the builder_ID from the original recordset and just displays the results. It DOES duplicate the school_district fields because i am assuming that they are different builder_ID's associated with them.

The problem is that i need to filter the school districts based on the builder_ID's so that only the correct ones show based on the search criteria

example:

user searches for zip code 08029

search result pages displays all the builders within 50 miles of the zip code 08029

then, i want to display the school districts that each of those builders has. Some builders may have the same school district so i want to prevent those duplicates from occuring.

Report · Sep 18, 2012

Ouch. I wish this was PHP. Would've been work it out for you very quickly. I know the basic parameters but not a pro ASP guy! Let's wait for an expert to throw in some advice. I seem interested in getting to know this too

Report · Sep 18, 2012

yes, i wish i knew PHP a little better too. thank you for looking tho

Report · Sep 18, 2012

>Then from here i have another table that has the builder_ID value listed

>with all of the school districts that they work within.

>

>What i want to do is to display the search results for the

>School district *** BUT NOT DISPLAY THE DUPLICATED VALUES *** here is the code i am using

I am an ASP guy, but there is a basic flaw of logic with what you are wanting, that is, you want to display the builder id, but only list the district once. You can't have it both ways. For example, if two builders are in the same district, which builder id do you want to display? Can you give us an example of some sample data along with the output you wish to achieve?

Report · Sep 18, 2012

Yes, the theory of what i am looking to accomplish is exactly as you have said, I realize there is a flaw in the logic, but i am trying to figure out what i can do to accomplish my goal

builder 1 - camden county school district

builder 2 - gloucester county school district

builder 3 - camden county school district

builder 4 - burlington county school district

so basically if all of these builders do fall within the search criteria, i want to display the school districts, but only list the 'camden county school district once'

from there, the user can select which school district they want to live in, and it will display the builder associated with that district. For the 'camden' one, it would display both builders

Report · Sep 18, 2012

>builder 1 - camden county school district

>builder 2 - gloucester county school district

>builder 3 - camden county school district

>builder 4 - burlington county school district

So to display a table like this without duplicating 'camden county school district', you would have to eliminate builder 3 - which does not make sense. A better approach would be to list the district alone. When they click on district they will be presented with the builders in that area.

So rather than using two recordsets, use a single recorset with a sub-select. I'm also not sure why you are using the HAVING clause in your first SELECT statement with no WHERE or GROUP BY Clause. I'll post an example SELECT statement in a bit....

Report · Sep 18, 2012

the reason that i want to have the HAVING clause is because lets say that:

builder 5 - is in West Virginia School District

but you as the user only wanted to show builders in a 50 mile radius from where you live. So when you choose the school District, you don't want to see West Virginia because that is too far away

thats why if i just run a basic recordset, it will show me ALL of the school districts instead of the ones that fall within that search builder criteria area.

does that make sense?

Report · Sep 18, 2012

>the reason that i want to have the HAVING

>clause is because lets say that:

The HAVING clause is used along with the GROUP BY clause when you are aggregating. You do not have any aggregate functions or GROUP BY clause so you should use the WHERE clause instead. Put another way, the WHERE clause filters rows, the HAVING clause filters groups. You have no groups.

Report · Sep 19, 2012

i have tried it both ways, but it still displays the duplicate values. I was just trying to the HAVING clause to see if it produced different results. I did use the 'GROUP BY' in my sql stmt but that didnt help either.

Is this just a lost cause? i won't be able to pull the duplicates out from what i want?

Report · Sep 19, 2012

>i have tried it both ways, but it still displays the duplicate values.

Sorry I was not clear. I was just pointing out that you should not use the HAVING clause if you are not filtering a group based on an aggregate value. You should use the WHERE clause to filter the rows based on their distance. This has nothing to do with your "duplicate" values.

That problem is caused by the fact that you are executing the SQL over and over again for each builder_id. To solve it you need a different approach. I was going to send you the code but ran out of time - I'll try to get it to you later today. What DBMS are you using?

Report · Sep 19, 2012

I am use MySQL database with a ODBC connection

if you could send me the code i would be extremely grateful. I have been pulling my hair out the last few days trying to get this to work

thanks again for everything!!

Report · Sep 19, 2012

Qustion for you: In your first SQL, you have a distance/location calculation that is used to filter the results. Do you also need to display this Distance anywhere in your output?

Report · Sep 19, 2012

I don't need to display those results, but obviously i need the results from the school district as a result of them - but to asnwer your question, no i don't need to display the radius distanc number

thank you

Report · Sep 19, 2012

OK, lets start by first testing if this query runs.

"SELECT builder_info_ID FROM home_detective.builder_info

WHERE ( 3959 * acos( cos( radians(39.8227054) ) * cos( radians(builder_info.lat ) ) * cos( radians(builder_info.lng) - radians(?)) + sin(radians(39.8227054)) * sin( radians(builder_info.lat)))) < 75"

You can just run it from your DBMS admin tool, but remember to populate the parameter value ? with some test value. It runs in Sybase SQL server but not sure if will in MySQL.

Let me know the results and I will send the next part.

Report · Sep 20, 2012

Yup, this part of the script works perfectly

Report · Sep 20, 2012

Great. So now try this query which uses the previous as a sub-query:

SELECT DISTINCT school_district FROM home_detective.school_district WHERE builder_ID IN

(SELECT builder_info_ID FROM home_detective.builder_info

WHERE ( 3959 * acos( cos( radians(39.8227054) ) * cos( radians(builder_info.lat ) ) * cos( radians(builder_info.lng) - radians(?)) + sin(radians(39.8227054)) * sin( radians(builder_info.lat)))) < 75)

Report · Sep 20, 2012

wow, i have never seen a duplicate select script written like that and didn't relaize i could do that. I am going to run this script and if this works i will be forever grateful to you.

thank you so much for all your assistance!!

Report · Sep 21, 2012

This script worked PERFECTLY !!! Thank you so much for your help!!

Report · Sep 21, 2012

Banbear, Please mark Bregent's answer correct and close this thread.

This not only helps poster gain points, but also helps other people with such similar issues by guiding them here with the correct answer!