2 inner joins - HTML Forums

mwood1 · 01-19-2005, 08:50 AM

Hi all,

any ideas how i can get this sql statement working?

SQLString = " SELECT Personnel.first_name, Personnel.surname, Personnel.extension_id, Office.office_name, Position.position " & _
" FROM Position INNER JOIN Personnel ON Position.pos_id = Personnel.position " & _
" Office INNER JOIN Personnel ON Office.office_id = Personnel.office " & _
" Where Personnel.position = " & request.form("position")

thanx in advance

putts · 01-19-2005, 10:45 AM

Couple of things....
1. you should provide aliases for any table used in a join (I think this is required)
2. a second join is done to the product of the first join not to one of the tables in the first join

Code:

SQLString = " SELECT Pers.first_name, Pers.surname, Pers.extension_id, Ofc.office_name, Pos.position " & _
" FROM Position Pos INNER JOIN Personnel Pers ON Pos.pos_id = Pers.position " & _
" INNER JOIN Office Ofc ON Ofc.office_id = Pers.office " & _
" Where Pers.position = " & request.form("position")

So, I assigned the tables the aliases of Pers, Pos and Ofc to fix the first thing.
Then, I simply did another INNER JOIN to the Office table on top of the join you'd already done involving Personnel and Position as opposed to doing two separate joins like you had.

Hope that does the trick for ya

mwood1 · 01-19-2005, 11:08 AM

hmmm, no still not working. But thanks for trying

putts · 01-19-2005, 01:39 PM

could you go into detail as to how it's not working.

Is it giving you a syntax error?
Is it just not returning any records?
etc.

mwood1 · 01-20-2005, 03:06 AM

No syntax error. Its just not returning the asp page. Here is the code now in full:
<%
dim objconn
dim strconnect
dim objrs
dim SQLstring

set objconn = Server.CreateObject("ADODB.Connection")
strconnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " & Server.MapPath("whos_who.mdb")

objconn.open strconnect
Set objrs = Server.CreateObject("ADODB.RecordSet")

SQLString = " SELECT Pers.first_name, Pers.surname, Pers.extension_id, Ofc.office_name, Pos.position " & _
" FROM Position Pos INNER JOIN Personnel Pers ON Pos.pos_id = Pers.position " & _
" INNER JOIN Office Ofc ON Ofc.office_id = Pers.office " & _
" Where Pers.position = " & request.form("position")
objrs.open SQLString, objconn, 3, 3
%>
<br>
Search Results

<table ID="Table1"><tr>
<%While objrs.EOF <> true%>
<td><%Response.Write objrs("first_name")%></td>
<td><%Response.Write objrs("surname")%></td>
<td><%Response.Write objrs("extension_id")%></td>
<td><%Response.Write objrs("position")%></td>
<%objrs.MoveNext%></tr>
<%Wend%>
</table>

<%
objrs.close
objconn.close
set objconn = Nothing

%>

thanx in advance

putts · 01-20-2005, 01:00 PM

just out of curiousity, change both joins to left outer joins.

If there's something wrong with your data, it may be preventing the join from returning anything (e.g. there's no office value in Personnel that matches up with the values of office_id in Office)

The other thing you can try if you have access to some tool where you can run queries directly against the database is to just response.write your sql statement and paste that into said tool.

mwood1 · 01-21-2005, 03:37 AM

Ok have tried changing the joins etc but nothing seems to work. I have built the query in access and this runs fine so i now have this statement: 'SELECT * FROM Q_position'

This pulls out all the information concerning staff members and their positions into an html table on the ASP page. However, as soon as i add the statement:
'WHERE position = request.form("position")'
nothing works.

Putts i am now at my wits end!! Do i specify the field as Q_position.position?? And is the concept of stored queries a good one??

Thanx for your help

afterburn · 01-21-2005, 09:14 AM

The issue is the results are being limited by your where clause. Your not checking the value of the variable before inserting into the query. Which is mostlikely null or empty string.

In SQL that would not really result in an error. But would cause no results.