Hello there!

I got this problem which I cant solve by myself!


after raning this sql i m getting some double values

999666 IT Organisations Autumn
999666 IT Organisations Autumn
999666 IT Organisations Autumn

can anyone explan why?

<%
sID=request.querystring("staff")

set myConnection=server.createobject("ADODB.connection ")
myConnection.open "Outline", "IWSDStudent", "mill"

mySQL = "select distinctrow semester.semester,coordination.unitID, unit.unitname,staff.name FROM semester, coordination, unit, staff where "
mySQL = mySQL & "coordination.staffID = " & sID & " and "
mySQL = mySQL & "unit.unitID=coordination.unitID and "
mySQL = mySQL & "coordination.staffID =staff.staffID"

set myRS = myConnection.execute(mySQL)
%>




Unit ID Unit Name Semester
999888 XYZ Analysis Autumn
999777 Resource Waste Optimisation Autumn
999666 IT Organisations Autumn
999666 IT Organisations Autumn
999666 IT Organisations Autumn
999888 XYZ Analysis Spring
999777 Resource Waste Optimisation Spring
999666 IT Organisations Spring
999666 IT Organisations Spring
999666 IT Organisations Spring
999888 XYZ Analysis Summer
999777 Resource Waste Optimisation Summer
999666 IT Organisations Summer
999666 IT Organisations Summer
999666 IT Organisations Summer

You have data coming from 4 tables (semester, coordination, unit, and staff), but you have only two joins (unit.unitID = coordination.unitIT and coordination.staffID = staffID). What about semester? You should have joins for every table. That would be my initial guess...