SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 multiple outer joins
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 06/27/2002 :  09:19:08  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Adam writes "Sql Server 2000
Windows 2000 server

Having trouble with nested multiple outer joins.
Found this syntax online but returns errors.

Select A.emailid,A.email,A.firstname,a.middleinitial,
a.lastname,a.phone,a.address,a.aptnumber,a.city,a.state,
a.zip,a.age,c.gender,f.maritalstatus,d.income,e.credit,
I.residence,B.education,h.occupation,g.numchildren,a.bitfieldcat
from
((((((((EmailAddresses a1 left outer join educationmapping B on (a1.education = b.educationid)) a2
left outer join gendermapping C on (a2.gender = c.genderid)) a3
left outer join Incomemapping D on (a3.annualincome = d.incomeid)) a4
left outer join creditmapping E on (a4.credithistory = e.creditid)) a5
left outer join maritalstatusmapping F on (a5.maritalstatus = f.maritalid)) a6
left outer join numchildrenmapping G on (a6.children = g.childrenid)) a7
left outer join occupationmapping H on (a7.occupation = h.occupationid)) a8
left outer join residencemapping I on (a8.residencestatus = i.residenceid)) a

where
((1 & a.BitFieldCat) > 0)"

MakeYourDaddyProud
Posting Yak Master

United Kingdom
184 Posts

Posted - 06/27/2002 :  09:23:52  Show Profile  Reply with Quote
... sniff, sniff, sniff. I can smell an Access query. Try removing all the braces and indenting as a first course of action.

Daniel Small MIAP
www.danielsmall.com IT Factoring
Go to Top of Page

acollins74
Yak Posting Veteran

USA
82 Posts

Posted - 06/27/2002 :  10:04:12  Show Profile  Reply with Quote
Still Can't seem to find the correct syntax for mult. outer joins.

Daniel's help was wonderful but something a little more specific would be nice also.

Go to Top of Page

MakeYourDaddyProud
Posting Yak Master

United Kingdom
184 Posts

Posted - 06/27/2002 :  10:56:40  Show Profile  Reply with Quote
Try this after your whole select list

FROM
EmailAddresses a1
left outer join educationmapping B on a1.education = b.educationid
left outer join gendermapping C on B.gender = c.genderid
left outer join Incomemapping D on C.annualincome = d.incomeid
left outer join creditmapping E on D.credithistory = e.creditid
left outer join maritalstatusmapping F on E.maritalstatus = f.maritalid
left outer join numchildrenmapping G on F.children = g.childrenid
left outer join occupationmapping H on G.occupation = h.occupationid
left outer join residencemapping I on H.residencestatus = i.residenceid

HTH

Daniel Small MIAP
www.danielsmall.com IT Factoring
Go to Top of Page

acollins74
Yak Posting Veteran

USA
82 Posts

Posted - 06/27/2002 :  14:17:35  Show Profile  Reply with Quote
Ok, This will not work b/c the mapping tables are not related to each other. The join must be nested.
The mapping tables are related to the EmailAddresses table by the ID columns in the mapping tables.

The database is set up this way because an everchanging asp page calls the mapping tables to populate several drop downs dynamically.

I appreciate your help!



Go to Top of Page

MakeYourDaddyProud
Posting Yak Master

United Kingdom
184 Posts

Posted - 06/27/2002 :  14:38:11  Show Profile  Reply with Quote
Can you post column lists of the mapping & email addresses tables? I can work this out for you.

Daniel Small MIAP
www.danielsmall.com IT Factoring
Go to Top of Page

setbasedisthetruepath
Used SQL Salesman

USA
992 Posts

Posted - 06/27/2002 :  14:38:23  Show Profile  Reply with Quote
I disagree with you that the joins must be nested. The parentheses in a FROM clause are optional (and in fact not desirable because they cause the SQL Server optimizer to consider a particular join order; see Rob's comments ) and you can write functionally equivalent expressions without them.

Post your DDL and describe in more detail how the data is structured.

Jonathan Boott, MCDBA

Edited by - setbasedisthetruepath on 06/27/2002 14:38:58
Go to Top of Page

acollins74
Yak Posting Veteran

USA
82 Posts

Posted - 06/27/2002 :  15:19:39  Show Profile  Reply with Quote
Hope this is enough information.

EMAILADDRESSES
EmailID bigint
Email varchar
RemoveRequest char
FirstName varchar
MiddleInitial varchar
LastName varchar
Phone varchar
Address varchar
AptNumber varchar
City varchar
State char
Zip varchar
Age int
Gender bit
MaritalStatus int
CreditHistory int
ResidenceStatus int
AnnualIncome int
Education int
Occupation int
Children int
BitFieldCat int
Counter int
MaxAllowed int
DateTime datetime

MAPPING TABLES
Id int
Name varchar

mapping tables prepopped..
eg:
OCCUPATIONMAPPING
Id Name
1 Consultant
2 Farmer
.
.
15 Singer

CREDITHISTORYMAPPING
Id Name
1 Excellent
2 Good
3 Average
4 Poor

etc...

Go to Top of Page

drymchaser
Aged Yak Warrior

USA
552 Posts

Posted - 06/28/2002 :  15:36:55  Show Profile  Reply with Quote
Try this:

from EmailAddresses a1
left outer join educationmapping B on a1.education = b.id
left outer join gendermapping C on convert(int, a1.gender) = c.id
left outer join Incomemapping D on a1.annualincome = d.id
left outer join creditmapping E on a1.credithistory = e.id
left outer join maritalstatusmapping F on a1.maritalstatus = f.id
left outer join numchildrenmapping G on a1.children = g.id
left outer join occupationmapping H on a1.occupation = h.id
left outer join residencemapping I on a1.residencestatus = i.id

Edited by - drymchaser on 06/28/2002 15:38:43
Go to Top of Page

acollins74
Yak Posting Veteran

USA
82 Posts

Posted - 07/01/2002 :  09:06:43  Show Profile  Reply with Quote
Crazy Thanks to Drymchaser.

Works Great!

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000