Solved

Please help with SQL combination of INNER JOIN AND LEFT JOIN

Posted on 2004-08-19
13
209 Views
Last Modified: 2013-12-24
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.
0
Comment
Question by:mdbbound
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 21

Expert Comment

by:pinaldave
ID: 11846432
Hi mdbbound,
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
0
 
LVL 15

Expert Comment

by:danrosenthal
ID: 11846505
what is the error message you are getting?
0
 

Author Comment

by:mdbbound
ID: 11846648
Oh it says joins are not supported.  

thanks
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 21

Expert Comment

by:pinaldave
ID: 11846665
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
0
 

Author Comment

by:mdbbound
ID: 11846763
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


0
 
LVL 21

Expert Comment

by:pinaldave
ID: 11846840
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
0
 
LVL 17

Expert Comment

by:anandkp
ID: 11849554
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.
0
 
LVL 35

Assisted Solution

by:mrichmon
mrichmon earned 500 total points
ID: 11853646
Not always true anadkp,

I have had queries that return no results in Access itself and yet work perfectly when called via CF to access and vice versa.

The access Query tool is not very good.


THe problem seems to be missing syntax....

I would write this in cf and see what you get :

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 tCUsers tcus ON tcur.CURoleid = tcus.CURoleid
LEFT JOIN tCUDept tcud ON tcud.CUDeptID = tcus.CUDeptID
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11853654
Note that this probably won't work directly in access, but should work through CF
0
 
LVL 17

Expert Comment

by:anandkp
ID: 11868871
mebbe ...
its something - i have NOT come across so far ...
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11871387
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
0
 

Author Comment

by:mdbbound
ID: 11958539
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.
0
 
LVL 35

Accepted Solution

by:
mrichmon earned 500 total points
ID: 11958593
You are correct, but that only applies when doing things like Query of Queries.  CF supports SENDING the Outer Join command through to a DB that does support it such as access and MS SQL
0

Featured Post

Don't Miss ATEN at InfoComm 2017!

Visit booth #2167 to see the  new ATEN VM3200 32 x 32 Modular Matrix Switch. Other highlights include the VE8950 4K HDMI Over IP Extender, VS1912 12-Port DP Video Wall Media Player  and VK2100 ATEN Control System. Register now with Free Pass Code ATEN288!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question