Complex table joining

[bostjank]

Hi!

I have two tables (members, fees) with one to many connection: one member can have more than 1 related record in 'fees' table.
How can I join the two tables so that I could show member info and data from up to 2 specific records from 'fees' in one row in HTML table


sSQL = "SELECT members.* FROM members LEFT JOIN fees ON members.MemberID = fees.MemberID WHERE (fees.Year = 2003 Or fees.Year = 2002"


This SQL retrieves all records, but each year is shown in seperate row. I guess I could retrieve Max MemberID, but then I would have to have another SQL to retrieve other values.

Any ideas?

Bostjan

[raf]

I don't quite understood all that. With this query you only get data from members.(not from both tabels)
It's no because two tables have a one to many relationship, that you need an outer join. A left outer join will get you all records form the members table (even if there are no matching records in the fees table. I suppose you only need records with matching fields in both tables --> so use an inner join (performs much quicker, normally)
This SQL retrieves all records, but each year is shown in seperate row. I guess I could retrieve Max MemberID, but then I would have to have another SQL to retrieve other values.
so what date exactly would you want? Total of all fees for each member? Max Member ID will only return one record (--> last inserted member).

[bostjank]

Oops, I forgot to add fees.* in the SQL statement. What I would need is a HTML table with data like this:

member.name member.surname member... fees data (for 2002) fees data (for 2002)

One line for each member.

Now I first get the list of all members that have an entry for 2002 or 2003 in the fees table. Then I loop through those MemberID's and I select all data I need for each member - so I hava a query for fees 2002 and a query for fees 2003.

But this is slow - I would like to do it it more efficient way.

[raf]

member.name member.surname member... fees data (for 2002) fees data (for 2002)
I suppose the last 2002 is a typo.


I don't think a simple sql statement can get you that. Access, i know, has some 'crostab' query features, which is basically what you need (i believe it's a stored procedure. You could look into Data Shaping (run a search here or on Google) but that only works with Jet databases so i never looked into it.

The most easy and universal way is simply do it in ASP. Select the fees for 2002 and 20032 in one recordset. At the start of your loop, check if the memberID is the same as the previous. If it isn't, open a new row , new column, print all the members info, close column, open column,print the fees for 2002, close column. If it is the same: open column, print fees 2003, close column, close row.
something like

dim member
member = 0 'to make sure the first run will start a new row
do while rsrecordsetname.EOF=False
if rsrecordsetname.Fields("memberID") <> member then
response.write("<tr><td>your memberinfo</td><td>fees2002</td>")
else
response.write("<td>fees2003</td></tr>")
end if
member = rsrecordsetname.Fields("memberID")
rsrecordsetname.MoveNext
loop

It will be quite fast. Unless you have hundreds of members, but then you'de get a realy long table (not very practical for the user) so you should do some recordsetpaging then, which would solve the speed problem as well.

[bostjank]

Thanks, I'll do that.