How and where do I add my outer join? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How and where do I add my outer join?

Author

Topic

fuzzyjon
Starting Member

15 Posts

Posted - 2008-04-11 : 04:58:37

Hello,

I have the following script which is *sort of working* !!

The problem I have is that I need to add an outer join to one of the tables and I don't know where to add it or what the syntax is.

Basically, anyone who has an 'STRA' role in the contacts_roles table does not usually have an email address (shown as communications.notes). However, because I don't have any outer joins in place, the script is ignoring everyone who has an 'STRA' role and only pulling back those with an 'STRE' role.

Any help would be much appreciated as to how and where I put my outer join.

Thanks so much.

Jon

SELECT contacts.label_name, contact_positions.position, contact_roles.role, contact_roles.organisation_number, communications.notes, organisations.status, organisations.name, addresses.address, addresses.town, addresses.county, addresses.postcode
FROM bmf.dbo.addresses addresses, bmf.dbo.communications communications, bmf.dbo.contact_positions contact_positions, bmf.dbo.contact_roles contact_roles, bmf.dbo.contacts contacts, bmf.dbo.organisations organisations
WHERE contact_roles.contact_number = contacts.contact_number AND communications.contact_number = contacts.contact_number AND organisations.organisation_number = contact_roles.organisation_number AND addresses.address_number = organisations.address_number AND contact_positions.contact_number = contacts.contact_number AND contact_positions.organisation_number = organisations.organisation_number AND ((contact_roles.role In ('STRE','STRA')) AND (organisations.status In ('BRAN','FULL','HOLD')))
ORDER BY organisations.name

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-11 : 05:09:40

Firstly You should change all non-ANSI joins to ANSI ones using INNER JOIN syntax.

From Table1 t1 INNER JOIN Table2 t2 on t1.somecol = t2.somecol
...

You should LEFT OUTER JOIN communications table with contacts table.

FROM communications m LEFT OUTER JOIN contacts c
on m.contact_number = c.contact_number
INNER JOIN ... -- other joins here

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

fuzzyjon
Starting Member

15 Posts

Posted - 2008-04-11 : 05:40:12

Hi there.... thanks for your help....

So, first of all.... changing the non-ANSI joins to ANSI...

Should my FROM section now read e.g.:-

FROM bmf.dbo.addresses addresses INNER JOIN bmf.dbo.organisations organisations on addresses.address_number = organisations.address_number, bmf.dbo.contact_roles contact_roles INNER JOIN bmf.dbo.organisations organisations on contact_roles.organisation_number = organisations.organisation_number,
bmf.dbo.contact_roles contact_roles INNER JOIN bmf.dbo.contacts contacts on contact_roles.contact_number = contacts.contact_number,
bmf.dbo.contact_positions contact_positions INNER JOIN bmf.dbo.contacts contacts on
contact_positions.contact_number = contacts.contact_number,
bmf.dbo.communications communications INNER JOIN bmf.dbo.contacts contacts on
communications.contact_number = contacts.contact_number

Thanks

Jon

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-11 : 05:52:56

Few corrections:

1. Use smaller aliases, if alias name is same as table name, the purpose of using alias is defeated

FROM bmf.dbo.addresses addr INNER JOIN bmf.dbo.organisations org on addr.address_number = org.address_number 
INNER JOIN bmf.dbo.contact_roles cr on cr.organisation_number = org.organisation_number

2. If you want all records from communications table, irrespective of related records in contacts table, you should define it as first table while joining

FROM communications m LEFT OUTER JOIN contacts c
on m.contact_number = c.contact_number
INNER JOIN contact_roles cr on cr.contact_number = c.contact_number
INNER JOIN ... --rest of the joins here

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

fuzzyjon
Starting Member

15 Posts

Posted - 2008-04-11 : 06:27:11

OK thanks....

So, would this be correct?

FROM communications m LEFT OUTER JOIN contacts c
on m.contact_number = c.contact_number
INNER JOIN contact_roles cr on cr.contact_number = c.contact_number
INNER JOIN addresses address_number on addresses.addres_number = organisations.address number
INNER JOIN contact_roles cr on cr.organisation_number = organisations.organisation_number
INNER JOIN contact_positions contact_number cr on contact_positions.contact_number = c.contact_number

Thanks

Jon

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-11 : 06:40:54

quote:
Originally posted by fuzzyjon

OK thanks....

So, would this be correct?

FROM communications m LEFT OUTER JOIN contacts c
on m.contact_number = c.contact_number
INNER JOIN contact_roles cr on cr.contact_number = c.contact_number
INNER JOIN addresses address_number on addresses.addres_number = organisations.address number
INNER JOIN contact_roles cr on cr.organisation_number = organisations.organisation_number
INNER JOIN contact_positions contact_number cr on contact_positions.contact_number = c.contact_number

Thanks

Jon

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

fuzzyjon
Starting Member

15 Posts

Posted - 2008-04-11 : 06:43:55

Thanks.... I'm getting there I think!

However, when I put this script into SQL Query Analyser I get the following error:-

"Server: Msg 1011, Level 15, State 1, Line 8
The correlation name 'cr' is specified multiple times in a FROM clause."

SELECT contacts.label_name, contact_positions.position, contact_roles.role, contact_roles.organisation_number,
communications.notes, organisations.status, organisations.name, addresses.address, addresses.town, addresses.county,
addresses.postcode
FROM communications m LEFT OUTER JOIN contacts c
on m.contact_number = c.contact_number
INNER JOIN contact_roles cr on cr.contact_number = c.contact_number
INNER JOIN addresses address_number on addresses.addres_number = organisations.address_number
INNER JOIN contact_roles cr on cr.organisation_number = organisations.organisation_number
INNER JOIN contact_positions contact_number on contact_positions.contact_number = c.contact_number

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-11 : 06:50:39

Oh..now I see you have duplicated table names as well. Also, aliases are not used properly.

FROM communications m LEFT OUTER JOIN contacts c on m.contact_number = c.contact_number
INNER JOIN contact_roles cr on cr.contact_number = c.contact_number
INNER JOIN organisations org on cr.organisation_number = org.organisation_number
INNER JOIN addresses addr on addr.addres_number = org.address number
INNER JOIN contact_positions cp on cp.contact_number = c.contact_number

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

fuzzyjon
Starting Member

15 Posts

Posted - 2008-04-11 : 06:54:17

Hi... thanks again....

I am now getting errors:-

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'contacts' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'contact_positions' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'contact_roles' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'contact_roles' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'communications' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'organisations' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'organisations' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'addresses' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'addresses' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'addresses' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'addresses' does not match with a table name or alias name used in the query.

When I run the query you wrote for me above...

Thanks

Jon

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-11 : 06:59:50

I think you didn't follow what an alias is. When you define an alias for a table, you need to use alias to refer to column of that table, instead of using actual table name.

In your case, when you define c as alias for contacts table, you should use c as a prefix to refer to any column of contacts table.
In your query, you are using aliases in WHERE clause but not in SELECT part, that's the reason you are getting errors.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

fuzzyjon
Starting Member

15 Posts

Posted - 2008-04-11 : 07:23:02

OK great.....

Almost there I think! I've amended the outer join slightly as I think I had it linked to the wrong tables.

However, I am still not getting those people with a ROLE of STRA who do not have an email address in the NOTES column of the COMMUNICATIONS table.

This is the revised query that I'm using:-

SELECT c.label_name, cp.position, cr.role, cr.organisation_number,
m.notes, org.status, org.name, addr.address, addr.town, addr.county,
addr.postcode
FROM communications m LEFT OUTER JOIN contact_roles cr on m.contact_number = cr.contact_number
INNER JOIN organisations org on cr.organisation_number = org.organisation_number
INNER JOIN contacts c on cr.contact_number = c.contact_number
INNER JOIN addresses addr on addr.address_number = org.address_number
INNER JOIN contact_positions cp on cp.contact_number = c.contact_number
WHERE cr.contact_number = c.contact_number AND m.contact_number = c.contact_number AND org.organisation_number = cr.organisation_number AND addr.address_number = org.address_number AND cp.contact_number = c.contact_number AND cp.organisation_number = org.organisation_number AND ((cr.role In ('STRE','STRA')) AND (org.status In ('BRAN','FULL','HOLD')))
ORDER BY cr.role

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources