hi guys,

ive got an aspx page connecting to an MS ACCESS database.

im trying to do two left joins!


here is my code for one left join which works:

Dim sqlCmd2 = "SELECT ur.student_id, ur.unit_id, ur.award, aw.title " _
& "FROM UNITREG ur LEFT JOIN AWARD aw ON ur.award = aw.id " _
& "WHERE ur.student_id = '"& id &"' " _
& "ORDER BY aw.title"

i want to add a join to another table which i think should be something like this:

Dim sqlCmd2 = "SELECT ur.student_id, ur.unit_id, ur.award, aw.title, un.title " _
& "FROM UNITREG ur LEFT JOIN AWARD aw ON ur.award = aw.id, UNIT un " _
& "UNITREG ur2 LEFT JOIN UNIT un ON ur2.unit_id = un.id " _
& "WHERE (ur.student_id = '"& id &"')" _
& "ORDER BY aw.title"

i pritty sure im on the right lines but get thie following error:

Syntax error (missing operator) in query expression 'ur.award = aw.id UNITREG ur2 LEFT JOIN UNIT un ON ur2.unit_id = un.id'.

i prosume this means the i am not seperating my two join statements correctly.

i have tried "," and AND with no luck!

any ideas???????

wrap your first JOIN in parantheses

Dim sqlCmd2 = "SELECT ur.student_id, ur.unit_id, ur.award, aw.title, un.title " _ 
& "FROM (UNITREG ur LEFT JOIN AWARD aw ON ur.award = aw.id, UNIT un " _ 
& "UNITREG ur2 ) LEFT JOIN UNIT un ON ur2.unit_id = un.id " _
& "WHERE (ur.student_id = '"& id &"')" _ 
& "ORDER BY aw.title"

look to be in the right places

some other code i tryed:

Dim sqlCmd2 = "SELECT ur.student_id, ur.unit_id, ur.award, aw.title, un.title "
_ & "FROM UNITREG ur LEFT JOIN AWARD aw ON ur.award = aw.id "
_ & "LEFT JOIN UNIT un ON ur.unit_id = un.id "
_ & "WHERE (ur.student_id = '"& id &"')"
_ & "ORDER BY aw.title"

chris,

i tryed this

Dim sqlCmd2 = "SELECT ur.student_id, ur.unit_id, ur.award, aw.title, un.title "
_ & "FROM (UNITREG ur LEFT JOIN AWARD aw ON ur.award = aw.id, UNIT un "
_ & "UNITREG ur2 ) LEFT JOIN UNIT un ON ur2.unit_id = un.id "
_ & "WHERE (ur.student_id = '"& id &"')"
_ & "ORDER BY aw.title"


and got following error:

Syntax error in JOIN operation.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error in JOIN operation.

thanks Chris,

i managed to get it sorted!!!!
i used the brackets and tidied up my code!

thanks for that!!!!!!!

Dim sqlCmd2 = "SELECT ur.student_id, ur.unit_id, ur.award, aw.title, un.title "
_ & "FROM (UNITREG ur LEFT JOIN AWARD aw ON ur.award = aw.id) "
_ & "LEFT JOIN UNIT un ON ur.unit_id = un.id "
_ & "WHERE (ur.student_id = '"& id &"')"
_ & "ORDER BY aw.title"

u r the man!!!!!

ok now i am trying three joins

Dim sqlCmd2 = "SELECT ur.student_id, ur.unit_id, ur.award, aw.title, un.title, re.title "
_ & "FROM (UNITREG ur LEFT JOIN AWARD aw ON ur.award = aw.id) "
_ & "(LEFT JOIN UNIT un ON ur.unit_id = un.id) "
_ & "LEFT JOIN RESULT re ON ur.grade = re.id "
_ & "WHERE (ur.student_id = '"& id &"')"
_ & "ORDER BY aw.title"


with following error:

Syntax error in FROM clause.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error in FROM clause.

any ideas??????

now it starts to get complicated with nested parantheses

move the parantheses at the beginning of the second join to before the first parantheses

_ & "FROM ((UNITREG ur LEFT JOIN AWARD aw ON ur.award = aw.id) " 
_ & "LEFT JOIN UNIT un ON ur.unit_id = un.id) " 
_ & "LEFT JOIN RESULT re ON ur.grade = re.id " 
_ & "WHERE (ur.student_id = '"& id &"')"

Excellent!

thanks a lot