mdbbound
asked on
Please help with SQL combination of INNER JOIN AND LEFT JOIN
Hi,
I have an sql combination of INNER JOIN AND LEFT JOIN. I did it in MS Access and tried to put it in my <cfquery>
Can someone please help me write this sql correctly:
SELECT tcur.CURoleid, tcur.CURolefunc, tcus.CUserID, tcus.CUlast, tcus.CUfirst,
tcus.CUMI, tcus.CUContact, tcus.CUEmail, tcus.CUDeptID, tcus.CURoleid, tcud.CUDeptID,
tcud.CUDeptNm FROM tCUserRole tcur INNER JOIN (tCUDept tcud LEFT JOIN tCUsers tcus ON tcud.CUDeptID = tcus.CUDeptID) ON tcur.CURoleid = tcus.CURoleid
the left join is neccesary because i would like to get the DeptNm. Not all users might belong to a department so if i don't use left outer join, I will not be able to get the users that does not belong to a department.
I have an sql combination of INNER JOIN AND LEFT JOIN. I did it in MS Access and tried to put it in my <cfquery>
Can someone please help me write this sql correctly:
SELECT tcur.CURoleid, tcur.CURolefunc, tcus.CUserID, tcus.CUlast, tcus.CUfirst,
tcus.CUMI, tcus.CUContact, tcus.CUEmail, tcus.CUDeptID, tcus.CURoleid, tcud.CUDeptID,
tcud.CUDeptNm FROM tCUserRole tcur INNER JOIN (tCUDept tcud LEFT JOIN tCUsers tcus ON tcud.CUDeptID = tcus.CUDeptID) ON tcur.CURoleid = tcus.CURoleid
the left join is neccesary because i would like to get the DeptNm. Not all users might belong to a department so if i don't use left outer join, I will not be able to get the users that does not belong to a department.
what is the error message you are getting?
ASKER
Oh it says joins are not supported.
thanks
thanks
Hi mdbbound,
that is not the error of your ColdFusion. That is ms access or any other data base you are using their error. What is your database now? mysql or other version of msacess.
Regards,
---Pinal
that is not the error of your ColdFusion. That is ms access or any other data base you are using their error. What is your database now? mysql or other version of msacess.
Regards,
---Pinal
ASKER
MS Access 2000.
This is actually first done in MS Access and I just followed it, retype in <cfquery>
SELECT tcur.CURoleid, tcur.CURolefunc, tcus.CUserID, tcus.CUlast, tcus.CUfirst,
tcus.CUMI, tcus.CUContact, tcus.CUEmail, tcus.CUDeptID, tcus.CURoleid, tcud.CUDeptID,
tcud.CUDeptNm FROM tCUserRole tcur INNER JOIN (tCUDept tcud LEFT JOIN tCUsers tcus ON ) ON tcur.CURoleid = tcus.CURoleid
in Access, it says that the sql is complex and I need to break it down into two.
But how would you rewrite this in such a way that i can get the third join (left join) in tcud.CUDeptID = tcus.CUDeptID
thanks
This is actually first done in MS Access and I just followed it, retype in <cfquery>
SELECT tcur.CURoleid, tcur.CURolefunc, tcus.CUserID, tcus.CUlast, tcus.CUfirst,
tcus.CUMI, tcus.CUContact, tcus.CUEmail, tcus.CUDeptID, tcus.CURoleid, tcud.CUDeptID,
tcud.CUDeptNm FROM tCUserRole tcur INNER JOIN (tCUDept tcud LEFT JOIN tCUsers tcus ON ) ON tcur.CURoleid = tcus.CURoleid
in Access, it says that the sql is complex and I need to break it down into two.
But how would you rewrite this in such a way that i can get the third join (left join) in tcud.CUDeptID = tcus.CUDeptID
thanks
Hi mdbbound,
well as guess before that this is problem with Access. I will suggest you ask this Que in MS access. Because they will know almost everything about MS Access behaviour and will guide you correct.
Regards,
---Pinal
well as guess before that this is problem with Access. I will suggest you ask this Que in MS access. Because they will know almost everything about MS Access behaviour and will guide you correct.
Regards,
---Pinal
If u used the Access Query Builder for creating the query - it may have got ambigious join conditins & hence may not work when u try & execute it
try executing it first in MSAcesss itself - if it runs there - then it wld also run thru CFQuery.
try executing it first in MSAcesss itself - if it runs there - then it wld also run thru CFQuery.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Note that this probably won't work directly in access, but should work through CF
mebbe ...
its something - i have NOT come across so far ...
its something - i have NOT come across so far ...
It deson't happen in most cases, but when you start getting into more complex queries (at least complex as far as access is concerned - they are still simple for other DB languages) then that is when it can happen
ASKER
I read that CF does not support outer joins. I'll try mrichmon's suggestion. Sorry it has been so long, I have been so busy.
Thanks for the input everyone, i'll get back to you.
Thanks for the input everyone, i'll get back to you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ti shoudl work fine with CF query as CFquery do not care for anything you put between them. I have kept all the compelx thing and it is upto the database to execute that. There is no relations. You should be albe to put that in the CFQuery and it should work.
Regards,
---Pinal