Left JOIN problem in asp

[maxpouliot]

Hi, how come this sql request :
SELECT choixClientele.id,choixClientele.choix,demandesClientele.id AS verification FROM choixClientele LEFT JOIN demandesClientele ON choixClientele.id = demandesClientele.clientele WHERE demandesClientele.demande=17 ORDER BY choix

does not return all the entries of my choixClientele table? I tried this too :
SELECT choixClientele.id,choixClientele.choix,demandesClientele.id AS verification FROM choixClientele LEFT JOIN demandesClientele ON choixClientele.id = demandesClientele.clientele AND demandesClientele.demande=17 ORDER BY choix
but it does not work in access

Can anyone help me?

[Old Pedant]

Here is why:
http://www.codingforums.com/showthread.php?p=818192#post818192
http://www.codingforums.com/showthread.php?p=818192#post818192

So the fix is easy:


SELECT CC.id, CC.choix, DC.id AS verification
FROM choixClientele AS CC LEFT JOIN demandesClientele AS DC
ON CC.id = DC.clientele AND DC.demande=17
ORDER BY CC.choix

[Old Pedant]

OOPS... Just noticed you said "in Access".

Access is fussy. It requires parentheses around the multiple conditions in an ON, thus:

SELECT CC.id, CC.choix, DC.id AS verification
FROM choixClientele AS CC LEFT JOIN demandesClientele AS DC
ON ( CC.id = DC.clientele AND DC.demande=17 )
ORDER BY CC.choix


The parentheses won't hurt with other databases, so it's probably a good idea to always use them.

By the way: If you enter that query into Access and then go back later to view the SQL form of the query, Access (at least Access97, Access2000, and Access2003) will *remove* the parentheses and then the query won't work! It is an error that I reported to Microsoft for EACH ONE of those products and never got fixed. It may or may not be fixed in Access2007. I'm not going to buy it to find out. But, anyway, not a problem from ASP, of course. Just make sure the parentheses are there.

[maxpouliot]

Thanks alot!

i did not know this thing about parenthesis in access! It will be very usefull!

Thanks!