Obviously, the two queries are different. 

In first query, anything from c is selected because you left joined the
other party. 

But in the second one, you left joined one table only, the result set then
joined the other tables. You loose any row from c table that does not match
in last the two joins. 

If Query 1 is a good written query if that is what you want. As for syntax,
the following might be a little bit clearer and faster (force compiler to
filter first before left join)

SELECT
c.ObjectID, s.sObjectID
FROM
dbo.[Case] c
left JOIN (Select  p.[Case] as pCase, subj_plntf.ObjectID as sObjectID 
	From dbo.Participant p
	JOIN dbo.ParticipantCategory pcat ON pcat.ObjectID 
p.ParticipantCategory
	JOIN dbo.Subject subj_plntf ON subj_plntf.ObjectID = p.Subject
) as s
ON c.ObjectID=s.pCase

Gary Xu




-----Original Message-----
From: Igor Savin [mailto:soniko@m...]
Sent: Wednesday, February 06, 2002 5:13 AM
To: sql language
Subject: [sql_language] RE: Join order


> But the results returned from Access don't vary from what you'd expect - 
it
> just needs ( ) for some reason...
> 
> Igor,
> Is there anything about the records that are not being returned by one 
query
> that you could use to determine what's going on?
> 
> Cheers
> Ken

Yes. Records from dbo.[Case] may have no records in dbo.Participant 
referred to [case]. As a result the 1st query return always recs from 
[case] regardless of content of Participant (absent fields simly is null 
in result)

The 2nd query skip recs from [case] which have no Participants recs.

I simly couldn't thnik out any other way to do this task but only 1st 
query helped me :( But if anybody give me advise how to rewrite this in 
way without such tricks I would be very glad to see new solution.

> :
> : Query 1
> : --------------------------------------------------------
> : SELECT
> : c.ObjectID,
> : dbo.GetSubjectFullName(subj_plntf.ObjectID)
> : FROM
> : dbo.[Case] c
> : left JOIN (
> :            dbo.Participant p
> :    JOIN dbo.ParticipantCategory pcat ON
> :                pcat.ObjectID = p.ParticipantCategory
> :    JOIN dbo.Subject subj_plntf ON subj_plntf.ObjectID = p.Subject
> : )
> :         ON p.[Case]=c.ObjectID
> :
> :
> : Query 2
> : --------------------------------------------------------
> : SELECT
> : c.ObjectID,
> : dbo.GetSubjectFullName(subj_plntf.ObjectID)
> : FROM
> : dbo.[Case] c
> : left JOIN dbo.Participant p ON p.[Case]=c.ObjectID
> :         JOIN dbo.ParticipantCategory pcat ON pcat.ObjectID 
> :                      p.ParticipantCategory
> :         JOIN dbo.Subject subj_plntf ON subj_plntf.ObjectID = p.Subject
> 
> 

$subst('Email.Unsub').


This electronic message transmission, including any attachments, contains information from PacifiCare Health Systems Inc. which may
be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not
the intended recipient, be aware that any disclosure, copying, distribution or use of the contents of this information is
prohibited.  

If you have received this electronic transmission in error, please notify the sender immediately by a "reply to sender only" message
and destroy all electronic and hard copies of the communication, including attachments.