LEFT JOIN, RIGHT JOIN.. aargh [Archive]

View Full Version : LEFT JOIN, RIGHT JOIN.. aargh

teyeheimans

11-06-2002, 04:18 PM

aargh, please help me.

This is the situation:

Ive got an Access database with 2 tables:

adressenlijst

id
adres
postcode
plaats
telefoon

namenlijst

naam
leeftijd
adres_id

namenlijst.adres_id contains the id to adressenlijst.id.

Ive got a script with list the addresses and shows the linked people with it. Only when the address has no people linked to it, the address isn't showed.

I know that i can fix that problem with a Join and instead to use a inner join, to use a Left Join. This wont work eather...

Whats wrong???

Here's my script:

<%

'maak database connectie object
Set DB_CONN = Server.CreateObject("ADODB.Connection")
DB_CONN.Open "FILEDSN=" & server.mappath("schoolApp.dsn")

Dim PageLength, NumPages, CurrentPage, Address, ShowTel

'aantl records per pagina
PageLength = 20

'Telefoonnummer laten zien of niet
ShowTel = True

If Request.QueryString("max") = "" Then
PageLength = 20
Else
PageLength = CInt(Request.QueryString("max"))
End If

'maak recordset object
Set RS = Server.CreateObject("ADODB.Recordset")
RS.PageSize = PageLength
RS.CacheSize = PageLength

'De query
'SQL_STRING = "SELECT * FROM adressenlijst LEFT JOIN namenlijst ON namenlijst.adres_id = adressenlijst.id ORDER BY adressenlijst.id"
SQL_STRING = "SELECT * FROM namenlijst RIGHT JOIN adressenlijst ON namenlijst.adres_id = adressenlijst.id" 'WHERE achternaam Like '" & Char & "%' ORDER BY achternaam"

'^ i tried a lot of these, but it seems to make no difference

'voer de query uit
RS.Open SQL_STRING, DB_CONN, adOpenStatic, adLockReadOnly, adCmdText

'paginanummer via url
If Request.QueryString("page") = "" Then
CurrentPage = 1
Else
CurrentPage = CInt(Request.QueryString("page"))
End If

NumPages = RS.PageCount

If CurrentPage > NumPages Then
CurrentPAge = NumPages
End If

If CurrentPage < 1 Then
CurrentPage = 1
End If

If NumPages = 0 Then
Response.Write("Geen records aanwezig!")
Else
RS.AbsolutePage = CurrentPage
%>
<body onLoad='document.main.max.value=<%=PageLength%>'>
<form name='main'>
<table width='100%' border=1 cellspacing=0>
<tr bgcolor=eeeee>
<td valign=top>
<table width=100% cellspacing=0 cellpadding=0 border=0>
<tr >
<td valign=top>
<b>Pagina:</b>
<% If CurrentPage > 1 Then %>
<a href='index.asp?pg=overzicht&page=<%=CurrentPage - 1%>&max=<%=PageLength%>' style='text-decoration: none;'>[ < ]</a>
<% End If
For i = 1 To NumPages
%> <a href='index.asp?pg=overzicht&page=<%=i%>&max=<%=PageLength%>' style='text-decoration: none;'>[ <%

If i = CurrentPage Then
Response.Write("<b>" & i & "</b>")
Else
Response.Write(i)
End If

%> ]</a>
<%
Next

If CurrentPage < NumPages Then
%><a href='index.asp?pg=overzicht&page=<%=CurrentPage + 1%>&max=<%=PageLength%>' style='text-decoration: none;'>[ > ]</a>
<%
End If
%>
</td>
<td align=right nowrap valign=top>
Aantal <i>leerlingen</i> per pagina:
<select name=max onChange="window.location.href='index.asp?pg=overzicht&page=1&max='+this.value">
<option value=5>5</option>
<option value=20>20</option>
<option value=50>50</option>
<option value=100>100</option>
<option value=250>250</option>
</select>
</td>
</tr>
</table>
</td>
</tR>
</form>
<%
RecordCount = 0

Do While RecordCount < PageLength And Not RS.eof

If Address <> RS("adres_id") Then
If Address <> "" Then %>
</table>
</td>
</tr>
</table>
</span>
</td>
</tr>
<%
End If
%>

<%
Address = RS("adres_id")
End If
%>
<tr>
<td><%=RS("achternaam") & ", " & RS("voornaam") & " " & RS("tussenvoegsel")%></td>
<td><%=RS("geslacht")%></td>
<td><%=RS("klas_leraarcode")%></td>
</tr><%
RecordCount = RecordCount + 1
RS.MoveNext
Loop %>
</table>
</td>
</tr>
</table>
</span>
</td>
</tr>
</table>
<%

End If

RS.Close
Set RS = Nothing
DB_CONN.Close
Set DB_CONN = Nothing
%>

plz help :(

BigDaddy

11-06-2002, 05:42 PM

I started to write a join statement for you, but then I noticed something. Check the field names in the code.

<td><%=RS("achternaam") & ", " & RS("voornaam") & " " & RS("tussenvoegsel")%></td>

I don't see these field names listed in the above database tables.

teyeheimans

11-07-2002, 02:23 PM

your right, ive wrote the tables so quick that i forgot some..

Because i'm kind of new with asp i tried the whole scipt again so that i can understand some more of it.

And believe it or not, but its working now!

this is my script now:

<%

'maak database connectie object
Set DB_CONN = Server.CreateObject("ADODB.Connection")
DB_CONN.Open "FILEDSN=" & server.mappath("schoolApp.dsn")

Dim PageLength, NumPages, CurrentPage, Last, ShowTel, a

'aantl records per pagina
PageLength = 20

'Telefoonnummer laten zien of niet
ShowTel = True

'controleer hoeveel records per pagina laten zien moeten worden
If Request.QueryString("max") = "" Then
PageLength = 20
Else
PageLength = CInt(Request.QueryString("max"))
End If

'maak recordset object
Set RS = Server.CreateObject("ADODB.Recordset")
RS.PageSize = PageLength
RS.CacheSize = PageLength

'De query
'SQL_STRING = "SELECT * FROM adressenlijst ORDER BY adressenlijst.id"
SQL_STRING = "SELECT * FROM adressenlijst LEFT Join namenlijst ON adressenlijst.id = namenlijst.adres_id ORDER BY adressenlijst.id, namenlijst.achternaam"
'SQL_QUERY = "SELECT achternaam, tussenvoegsel, voornaam, klas_leraarcode, geslacht FROM namenlijst LEFT JOIN adressenlijst ON namenlijst.adres_id = adressenlijst.id ORDER BY adres_id"

'voer de query uit
RS.Open SQL_STRING, DB_CONN, adOpenStatic, adLockReadOnly, adCmdText
'Set BT = DB_CONN.Execute(SQL_QUERY)

'paginanummer via url
If Request.QueryString("page") = "" Then
CurrentPage = 1
Else
CurrentPage = CInt(Request.QueryString("page"))
End If

NumPages = RS.PageCount

If CurrentPage > NumPages Then
CurrentPAge = NumPages
End If

If CurrentPage < 1 Then
CurrentPage = 1
End If

If NumPages = 0 Then
Response.Write("Geen records aanwezig!")
Else
RS.AbsolutePage = CurrentPage
%>
<body onLoad='document.main.max.value=<%=PageLength%>'>
<form name='main'>
<table width='100%' border=1 cellspacing=0>
<tr bgcolor=eeeee>
<td valign=top colspan=3>
<table width=100% cellspacing=0 cellpadding=0 border=0>
<tr>
<td valign=top><b>Pagina:</b>
<% If CurrentPage > 1 Then %>
<a href='index.asp?pg=overzicht&page=<%=CurrentPage - 1%>&max=<%=PageLength%>' style='text-decoration: none;'>[ < ]</a>
<% End If

For i = 1 To NumPages
%> <a href='index.asp?pg=overzicht&page=<%=i%>&max=<%=PageLength%>' style='text-decoration: none;'>[ <%
If i = CurrentPage Then
Response.Write("<b>" & i & "</b>")
Else
Response.Write(i)
End If
%> ]</a> <%
Next

If CurrentPage < NumPages Then
%><a href='index.asp?pg=overzicht&page=<%=CurrentPage + 1%>&max=<%=PageLength%>' style='text-decoration: none;'>[ > ]</a>
<%
End If
%>
</td>
<td align=right nowrap valign=top>
Aantal <i>records</i> per pagina:
<select name=max onChange="window.location.href='index.asp?pg=overzicht&page=1&max='+this.value">
<option value=5>5</option>
<option value=20>20</option>
<option value=50>50</option>
<option value=100>100</option>
<option value=250>250</option>
</select>
</td>
<td align=right>
<img src='images/new.gif' style='cursor:hand' onClick='window.location.href="index.asp?pg=editAdres"'>
</td>
</tr>
</table>
</td>
</tR>
</form>
<%
RecordCount = 0

Last = ""

Do While RecordCount < PageLength And Not RS.eof
a = a + 1
If Last <> RS("id") And Last <> "" Then %>
</table>
</td></tr></table>
</span></td>
<td align=right valign=top>
<img src='images/edit.gif' style='cursor:hand' title='Adres wijzigen' OnClick='window.location.href="index.asp?pg=editAdres&editid=<%=RS("id")%>"'>
</td>
<td width=13 valign=top>
<img src='images/delete.gif' title='Adres verwijderen' onClick="deleteAdres(<%=RS("id")%>)" style='cursor:hand'>
</td>
</tr>
<%
End If
If Last <> RS("id") Then
%>
<tr><td width=100%><font style='cursor:hand' onClick='expandit(this)'>
<%
Response.write(RS("adres") & " ")
If ShowTel = True Then
Response.write(" - " & RS("telefoon"))
End If
%></font><span style='display:none' style=&{head};>
<table>
<tr><td><b>Adres</b><%=RS.RecordCount & "-" & i & "-" & RecordCount%></td><td width=30><b>:</b></td><td><%=RS("adres")%></td></tr>
<tr><td><b>Postcode</b></td><td width=30><b>:</b></td><td><%=RS("postcode")%></td></tr>
<tr><td><b>Woonplaats</b></td><td width=30><b>:</b></td><td><%=RS("woonplaats")%></td></tr>
<tr><td><b>Telefoonnummer</b></td><td width=30><b>:</b></td><td><%=RS("telefoon")%></td></tr>
<tr><td valign=top><b>Leerling(en)</b></td><td valign=top width=30><b>:</b></td><td>
<table cellspacing=0 border=1 cellpadding=4>
<tr><td><i>Naam</i></td><td><i>Geslacht</i></td> <td><i>Klas & leraarcode</i></td></tr>
<% End If

If RS("achternaam") <> "" Or RS("tussenvoegsel") <> "" Or RS("voornaam") <> "" Or RS("geslacht") <> "" Then %>

<tr><td><%=RS("achternaam") & ", " & RS("tussenvoegsel") & " " & RS("voornaam")%></td><td><%=RS("geslacht")%></td><td><%=RS("klas_leraarcode")%></td></tr>
 <%
Else %>

<tr><td>-</td><td>-</td><td>-</td></tr>
 <%
End If
Last = RS("id")

RecordCount = RecordCount + 1
RS.MoveNext
Loop

RS.MoveLast
%>
</table>
</td></tr></table>
</span></td>
<td align=right valign=top>
<img src='images/edit.gif' style='cursor:hand' title='Adres wijzigen' OnClick='window.location.href="index.asp?pg=editAdres&editid=<%=RS("id")%>"'>
</td>
<td width=13 valign=top>
<img src='images/delete.gif' title='Adres verwijderen' onClick="deleteAdres(<%=RS("id")%>)" style='cursor:hand'>
</td>
</tr>
<%

End If

RS.Close
Set RS = Nothing
DB_CONN.Close
Set DB_CONN = Nothing
%>
</table>

BigDaddy

11-08-2002, 02:47 AM

I didn't figure it was missing field names. If you try to write out the value of a field that's not in the recordset, it'll scream at you that you're missing that, not just leave out that piece of data.