recordset with joined tables

[x-man]

Does anyone know a good way of searching a recordset with joined tables and grouping the records (it's a one to many relationship) so that the fields belonging to the table with the primary key are only shown once?

So for example I have a companies table and and offices table where a company can have multiple offices and I use a linked table to join the two. I need to be able to search both the company and office tables and I want my ASP to display:

Acme
>London
>New York
>Paris

Rather than:

Acme
>London

Acme
>New York

Acme
>Paris

I've managed to use conditions to get the results to look the way I want but I then find it impossible to page through the records as in the example above there are actually 3 records but I need to to count them as 1 record (grouped by companyID) and each Company can have a different number of offices making it difficult to just have 10 records per page as 10 records isn't 10 companies.

I'd be grateful for any suggestions!

[tboss132]

You didn't let us see any of your code but I think I understand what you need. I'll advise you to use join statements.
See here (http://www.w3schools.com/sql/sql_join.asp) for more info.
If you're still having problems, post some of the code here and we'll see what can be done.

[x-man]

Thanks, I am using joins and I'm getting out all the data I need I'm just having problems in that when I display it the way I want I can't page through the records.

Let's say my recordset returns the company name from companies and using a link table (officematch) to offices it pulls out the office name(s) for each of those companies:
SELECT Companies.CompanyName, Offices.OfficeName
FROM (Companies LEFT JOIN OfficeMatch ON Companies.CompanyID = OfficeMatch.CompanyID) LEFT JOIN Offices ON OfficeMatch.OfficeID = Offices.OfficeID;

I end up with a recordset which looks something like:
CompanyName | OfficeName
-----------------------------
TestCo | London
TestCo | Toronto
Acme | London
Acme | New York
Acme | Paris

Now I don't want the company name displaying for every record on my ASP page - I want the Offices grouped under the company name so it displays something like:

Acme
>London
>New York
>Paris

Testco
>London
>Toronto

So to achieve this when I'm looping through the records I only write out the company name when I reach a new company:


<table>
<tr><th>Company</th><th>Office</th></tr><%
<%
strCompanyName = ""
Do While Not oRS.EOF
%>

<tr><td>

<%
If oRS("CompanyName") <> strCompanyName Then
strCompanyName = oRS("CompanyName")
%>

<%= strCompanyName %>

<% Else %>

&nbsp;

<% End If %>

</td>
<td><%=oRS("OfficeName")%></td></tr>

<%
oRS.MoveNext
Loop
%>

</table>

Which all displays fine but then I want to break my records down into 10 Companies per page but since 10 companies is not the same as 10 records I get stuck paging through the recordset.

So in the example above Acme and Testco are 2 companies but Acme actually consists of 3 records in my joined recordset and Testco is 2 records so whilst I'd like to treat them as 2 records for paging purposes they are really 5 records in the recordset and some companies may just have one office (and be 1 record) whilst others may have 10 offices (and be 10 records)

I'm looking for a way of accurately paging through my recordset and having 10 companies per page rather than 10 records but I'm not sure if there's a way of doing it or whether I need to use a different way of accessing my the records rather than using joined tables in one SQL statement.

Sorry for the long post but it's kind of tricky to explain :D

[Bullschmidt]

Does anyone know a good way of searching a recordset with joined tables and grouping the records (it's a one to many relationship) so that the fields belonging to the table with the primary key are only shown once?
In general I'd say within your looping compare CompanyName with the CompanyName of the previous record (perhaps to be stored in a variable called CompanyNamePrev) and only if it's different then do some of the stuff.

If CompanyName <> CompanyNamePrev Then
...
End If

EDIT - OOPS, SORRY IT LOOKS LIKE YOU ALREADY GOT PAST THIS PART OF THE PROBLEM. :)

[r0ck1t]

My suggestion is that you should create two loop counters, one that counts the records you have gone through one counts the company names, when the company name counter is ten you stop the loop send the number of records to the page as part of the next page link and make the db start from that record number so you don't see the same ten companies....


<table>
<tr>
<th>Company</th>
<th>Office</th>
</tr>
<%
strCompanyName = ""
Response.Write("<tr><td>")

Do While Not oRS.EOF AND intCompanyCount < 10
If oRS("CompanyName") <> strCompanyName Then
strCompanyName = oRS("CompanyName")
Response.Write("strCompanyName")
intCompanyCount = intCompanyCount + 1
Else
Response.Write("&nbsp;")
End If
Response.Write("</td><td>oRS("OfficeName")</td></tr>")
intRecordCount = intRecordCount + 1
oRS.MoveNext
Loop
%>
</table>


Unchecked and untested code. I assume you can figure out what needs to be set for the new variables and how to do it. etc. No responsibility for damages etc.

Hopefully that might help.

[x-man]

Thanks guys, I'll try that second loop counter.

:thumbsup:

[cehenke]

Part 3 of this article has the meat of the issue but read the whole thing to learn about it - http://www.4guysfromrolla.com/webtech/092599-1.shtml

CeH

[x-man]

Thanks CeH,

I've tried datashaping and it works well except I'm still unable to figure out a way of paging through the records 10 companies per page rather than 10 records (as 1 company may have any number of child records which makes paging by 10 records kind of senseless)

[cehenke]

I don't know how comfortable you are using stored procedures (personally I'm not good with them) but I googled on recordset paging and found this page, the code looks straightforward enough, don't know about the implementation though.

http://www.wwwcoder.com/main/parentid/191/site/2124/68/default.aspx

[cehenke]

The other thing that comes to mind is to figure out where to insert the traditional recordset paging code into the shaping code so that is pages on the the parent recordset (the company). I have done this but I can't come up with the page right now, it just takes a little time to get the code in the right order but if you can get your mind around it then this works like a charm. This page has some info on paging if you need it. http://authors.aspalliance.com/brettb/EasyADORecordSetPaging.asp or you can google it and find a hundred other pages that show you how. CeH