How do I present one-to-many relationships in my ASP page?

Often you need to show results from multiple tables in one unified interface. Let's say you have the following schema (geared to SQL Server):

CREATE TABLE artist
(
[id] INT IDENTITY,
[name] VARCHAR(32)
)

CREATE TABLE album
(
[artist_id] INT,
[name] VARCHAR(32)
)

INSERT INTO artist(name) VALUES('Michael Jackson')
INSERT INTO artist(name) VALUES('ABBA')
INSERT INTO artist(name) VALUES('Tragically Hip')

INSERT INTO album(artist_id,name) VALUES(1,'Bad')
INSERT INTO album(artist_id,name) VALUES(1,'Very Bad - Worst Hits')

INSERT INTO album(artist_id,name) VALUES(2,'Gold')

INSERT INTO album(artist_id,name) VALUES(3,'Self-Titled')
INSERT INTO album(artist_id,name) VALUES(3,'Up to Here')
INSERT INTO album(artist_id,name) VALUES(3,'Road Apples')
INSERT INTO album(artist_id,name) VALUES(3,'Fully Completely')
INSERT INTO album(artist_id,name) VALUES(3,'Day For Night')
INSERT INTO album(artist_id,name) VALUES(3,'Trouble at the Henhouse')
INSERT INTO album(artist_id,name) VALUES(3,'Live Between Us')
INSERT INTO album(artist_id,name) VALUES(3,'Phantom Power')
INSERT INTO album(artist_id,name) VALUES(3,'Music@Work')

The results you want in ASP are:

ABBA
Gold

Michael Jackson
Bad
Very Bad - Worst Hits

Tragically Hip
Self-Titled
Up to Here
Road Apples
Fully Completely
Day For Night
Trouble at the Henhouse
Live Between Us
Phantom Power
Music@Work

Now, what I see many people attempting in ASP is something like this (and I'll keep HTML simple for brevity):

<%
sql = "SELECT id,name FROM artist ORDER BY name"
set rs = conn.execute(sql)
do while not rs.eof
response.write rs("name") & ""
sql2 = "SELECT name FROM album WHERE artist_id=" &_
rs("id") & " ORDER BY name"

set rs2 = conn.execute(sql2)
do while not rs2.eof
response.write rs2("name") & " "
rs2.movenext
loop
response.write " "
rs.movenext
loop
%>

There are much more efficient ways to do this than creating multiple recordsets and nesting them. This is what joins are for. Imagine the above code if you then had a table with the song titles on the album? And then another table listing the bands who have covered each song? And then a table further to that listing the albums of each of THOSE bands?

Creating nested recordsets is very inefficient. Let's look at the join solution to the above:

<%
sql = "SELECT " &_
"artistName = artist.name, " &_
"albumName = album.name " &_
"FROM " &_
artist, " &_
album " &_
"WHERE " &_
"artist.id = album.artist_id " &_
"ORDER BY " &_
"artist.name, " &_
"album.name"

currentArtist = ""
set rs = conn.execute(sql)
do while not rs.eof
if rs("artistName") <> currentArtist then
response.write rs("artistName") & ""
currentArtist = rs("artistName")
end if
response.write rs("albumName") & " "
rs.movenext
loop
%>

One recordset, getting all your results, without causing extra strain on the database by executing multiple statements consecutively. One large recordset is always more efficient than multiple recordsets obtaining the same amount of data.

Now, one problem you might come acrosss is, what if an artist has no albums, but you still want to show the artist name? Imagine adding the following artist, who couldn't possibly have any record deals in effect:

INSERT INTO artist(name) VALUES('Aaron Bertrand')

Now, the query and ASP code to still return this value is as follows (note how little it changes):

<%
sql = "SELECT " &_
"artistName = artist.name, " &_
"albumName = ISNULL(album.name,'-') " &_
"FROM " &_
"artist " &_
"LEFT JOIN " &_
"album " &_
"ON " &_
"artist.id = album.artist_id " &_
"ORDER BY " &_
"artist.name, " &_
"album.name"

currentArtist = ""
set rs = conn.execute(sql)
do while not rs.eof
if rs("artistName") <> currentArtist then
response.write rs("artistName") & ""
currentArtist = rs("artistName")
end if
if rs("albumname") <> "-" then
response.write rs("albumName") & " "
else
response.write "No albums for this artist. "
end if
rs.movenext
loop
%>

We do a LEFT OUTER JOIN so that artists are included even if they have no matching rows in the albums title.

Of course, we would use stored procedures here, however the above methodology will work against Access, MySQL, etc.



// home // advanced search // news // categories // sql build chart // downloads // statistics
ASP FAQ

How do I present one-to-many relationships in my ASP page?

Related Articles