multiple outer joins - SQL Server Forums

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)

multiple outer joins

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-27 : 09:19:08

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

184 Posts

Posted - 2002-06-27 : 09:23:52

... 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

acollins74
Yak Posting Veteran

82 Posts

Posted - 2002-06-27 : 10:04:12

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.

MakeYourDaddyProud

184 Posts

Posted - 2002-06-27 : 10:56:40

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

acollins74
Yak Posting Veteran

82 Posts

Posted - 2002-06-27 : 14:17:35

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!

MakeYourDaddyProud

184 Posts

Posted - 2002-06-27 : 14:38:11

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

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-27 : 14:38:23

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

acollins74
Yak Posting Veteran

82 Posts

Posted - 2002-06-27 : 15:19:39

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...

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2002-06-28 : 15:36:55

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

acollins74
Yak Posting Veteran

82 Posts

Posted - 2002-07-01 : 09:06:43

Crazy Thanks to Drymchaser.

Works Great!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources