Query - Outer J oin- SQL 92 Compatability

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query - Outer J oin- SQL 92 Compatability

Author

Topic

manikandan
Starting Member

35 Posts

Posted - 2002-08-30 : 07:53:03

Following syntax is acceptable in sybase and the query gives the expected result.
What is the equivalent SQL query

SELECT Org1.OrganizationDescription, Org1.OrganizationDescription,
Org2.OrganizationDescription, EA.EMailAddress
FROM Organization Org1, Organization Org2, OrganizationRelationship ORel,
EMailAddress EA, EMailObject eO
WHERE EA.resourceid = Org1.organizationid and
EA.emailaddresstype = 'SMTP' and
EA.PrimaryResourceAddress = 'Y' and
EA.ResourceType = 'Organization' and
eO.EMailObjectType <> 'Distrib List' and
eO.EMailObjectType <> 'Public List Sec' and
EA.EMailType = eO.EMailType and
EA.EMailSite = eO.EMailSite and
EA.MailBoxID = eO.MailBoxID and
Org1.OrganizationID *= ORel.ChildOrganizationID and
Org2.OrganizationID =* ORel.ParentOrganizationID and
ORel.OrganizationRelationshipType = 'Home Cost Center' and
Org1.OrganizationType NOT IN
('Conference Room', 'Private Room', 'Equipment')

Cheers
Manikandan

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-30 : 09:34:45

replace the *= with left outer join

Organization Org1
left outer join OrganizationRelationship ORel
on Org1.OrganizationID = ORel.ChildOrganizationID
left outer join Organization Org2
on Org2.OrganizationID = ORel.ParentOrganizationID
join EMailAddress EA
on EA.resourceid = Org1.organizationid and
EA.emailaddresstype = 'SMTP' and
EA.PrimaryResourceAddress = 'Y' and
EA.ResourceType = 'Organization'
join EMailObject eO
on
EA.EMailType = eO.EMailType and
EA.EMailSite = eO.EMailSite and
EA.MailBoxID = eO.MailBoxID and
eO.EMailObjectType <> 'Distrib List' and
eO.EMailObjectType <> 'Public List Sec'
where
ORel.OrganizationRelationshipType = 'Home Cost Center' and
Org1.OrganizationType NOT IN
('Conference Room', 'Private Room', 'Equipment')

*= is valid in sql server but a *= b *= c isn't - but can be done with this syntax.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

manikandan
Starting Member

35 Posts

Posted - 2002-08-31 : 04:54:40

Thanks for the reply.

But some problems are there. The number of record that is returned by sybase is different from that SQL. (i did a DTS from Sybase to SQL) and ran these queries.

i understand the syntatical problems in the query which i've sent.

Until this point the query works.

SELECT Org1.OrganizationDescription, Org1.OrganizationDescription,
Org2.OrganizationDescription, EA.EMailAddress
FROM Organization Org1, Organization Org2, OrganizationRelationship ORel,
EMailAddress EA, EMailObject eO
WHERE EA.resourceid = Org1.organizationid and
EA.emailaddresstype = 'SMTP' and
EA.PrimaryResourceAddress = 'Y' and
EA.ResourceType = 'Organization' and
eO.EMailObjectType <> 'Distrib List' and
eO.EMailObjectType <> 'Public List Sec' and
EA.EMailType = eO.EMailType and
EA.EMailSite = eO.EMailSite and
EA.MailBoxID = eO.MailBoxID and
Org1.OrganizationID *= ORel.ChildOrganizationID .

But after this point, the original query does a right outer join with Orel. From what i understand, sybase will consolidate the result sets until now and do a right outerjoin with ORel table.

*= orel.childorganizationid

Won't the result set be affected by doing a left outerjoin with org2 table. (i mean by doing a right outer join, null will be inserted on those columns on the left hand side, if it does not have any matching record with the right table. Will it be the same if you do a left outer join with the other table like what you have done here).

My SQL knowledge is very limited. I could be completely wrong here. Please tell me on where i am erring and also i find problems in the where and on clause.

These two conditions are included in the where clause.
ORel.OrganizationRelationshipType = 'Home Cost Center' and
Org1.OrganizationType NOT IN
('Conference Room', 'Private Room', 'Equipment')

Shouldn't it be included in the on clause. If it is a inner join it is not a problem. But being a outer join, the result set will have an impact on including this in the where clause instead of On clause.

If anyone could come up with a set of rules that needs to be followed while moving from old syntax to new syntax, it will be wonderful.

And if someone could solve this query also, it will be very very useful.

Thanks

cheers
manikandan

Cheers
Manikandan

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-02 : 08:28:30

you have
Org1 *= ORel
Org2 =* ORel

which should be the same as
Org1 *= ORel
ORel *= Org2

which should be the same as
Org1
left outer join ORel
left outer join Org2

the problem probably comes from whether filter clauses are included in the join or the where clause

a left outer join b on a.id = b.id and b.fld = 1

this gives all rows from a and rows from b where b.fld = 1

a left outer join b on a.id = b.id
where b.fld = 1

This only includes fields from the join where b.fld = 1 (i.e. is the same as an inner join as it excludes nulls)

a left outer join b on a.id = b.id
where (b.fld = 1 or b.fld is null)

includes joined records where b.fld = 1 or those where no rec exists in b.
Excludes recs from a where a join exists but all b recs have fld <> 1.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

manikandan
Starting Member

35 Posts

Posted - 2002-09-03 : 09:22:50

Hi nr,

Thanks a lot. I just added the checking for is null in the where clause and it worked properly.

And also i understood the concept.

Thanks to SQL Team and nr.

cheers
manikandan

Cheers
Manikandan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources