Displaying all results but filtered 1

[jamiehook01]

Hi i am trying to display all records but only the rating from a curently logged in user (asp.net) but with my statement as it currently is it only returns records that the user has rated! can any one help?

rs.Open("SELECT * FROM [Photo] Photo LEFT JOIN [Rating] Rating ON Photo.PhotoID = Rating.PhotoNO" _
& " WHERE Rating.MemberID=" & UsrrID & "", cs, adOpenDynamic)

 

[PHV]

rs.Open("SELECT * FROM [Photo] Photo LEFT JOIN [Rating] Rating ON Photo.PhotoID = Rating.PhotoNO" _
& " AND Rating.MemberID = " & UsrrID, cs, adOpenDynamic)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jamiehook01]

Thank you for your response, i now get the error
Join expression not supported.
i have tried left and right join

 

[PHV]

Seems you use JetSQL (msaccess).
As you posted in the ANSI SQL forum, I gave you an ANSI SQL SQL answer.
You may try this:
rs.Open("SELECT * FROM Photo LEFT JOIN (SELECT * FROM Rating WHERE MemberID = " & UsrrID _
& ") R ON Photo.PhotoID = R.PhotoNO", cs, adOpenDynamic)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jamiehook01]

Thank you that worked great!!! sorry about the wrong forum