Home » SQL & PL/SQL » SQL & PL/SQL » Please, need an explanation of select results with multiple left joins (Oracle 10g)
icon5.gif  Please, need an explanation of select results with multiple left joins [message #307878] Thu, 20 March 2008 04:10
rimma_nh
Messages: 1
Registered: March 2008
Junior Member
Hello to everyone!
I'm new to this forum, sorry if i didn't follow any posting rules.

Please, can anyone explain differences in execution of below statements.

Background. I have 3 tables:

  1. SessionConstants - have many records (filtering settings of different users)
  2. Members - have many records (list of members which i would like to filter using filtering criteria of certain user)
  3. ICTypes - list of lookup values for icType of member (optional). One to many relationship.


I would like to get a list of members and their ictypes filtered by values from sessionconstants of certain user (userID = 181 and userObjID = 101) or if there is no match select should return that record from sessionconstants.

I execute this query:
Select members.*, icTypes.*, sessionConstants.*
From  sessionconstants 
left join  members
left join icTypes 
on members.ictype = icTypes.instanceID 
on (
    sessionconstants.userID = 181 and
    sessionconstants.userObjID = 101 and   
    member.regDate >= sessionconstants.startDate and 
    member.regDate <= sessionconstants.endDate
) 
Where 
sessionconstants.userID = 181 and sessionconstants.userObjID = 101


I don't know why this select returns all records from Members table (and those with unwanted regDate). If i modify query (remove icTypes table from query, or change sequence of left join .. on, or remove sessionconstants conditions from left join .. on part) then query returns correct result. Here is correct result queries:

1) No icTypes table:
Select members.*, sessionConstants.*
From  sessionconstants 
left join  members
on (
    sessionconstants.userID = 181 and
    sessionconstants.userObjID = 101 and   
    member.regDate >= sessionconstants.startDate and 
    member.regDate <= sessionconstants.endDate
) 
Where 
sessionconstants.userID = 181 and sessionconstants.userObjID = 101


2)Changed sequence of left joins:
Select members.*, icTypes.*, sessionConstants.*
From  sessionconstants 
left join  members
on (
    sessionconstants.userID = 181 and
    sessionconstants.userObjID = 101 and   
    member.regDate >= sessionconstants.startDate and 
    member.regDate <= sessionconstants.endDate
) 
left join icTypes 
on members.ictype = icTypes.instanceID 
Where 
sessionconstants.userID = 181 and sessionconstants.userObjID = 101


3)Removed sessionconstants.userID = 181 and sessionconstants.userObjID = 101 condition for left join:

Select members.*, icTypes.*, sessionConstants.*
From  sessionconstants 
left join  members
left join icTypes 
on members.ictype = icTypes.instanceID 
on (
    member.regDate >= sessionconstants.startDate and 
    member.regDate <= sessionconstants.endDate
) 
Where 
sessionconstants.userID = 181 and sessionconstants.userObjID = 101


Why that nested joins select is returning all records from Members? What am i doing wrong in initial query?

Thank you for your time,
highly appreciate an explanation.

[Updated on: Thu, 20 March 2008 04:39]

Report message to a moderator

Previous Topic: How to ignore ORA-01927 and proceed with loop process
Next Topic: Disable a Trigger by another trigger
Goto Forum:
  


Current Time: Sun Dec 04 00:51:27 CST 2016

Total time taken to generate the page: 0.04301 seconds