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
 Problem with an outer join - please help!!

Author  Topic 

fuzzyjonclay
Starting Member

9 Posts

Posted - 2014-04-22 : 08:08:26
Hi there

I am having a problem with the following script, and wondered if someone could please offer me some help.

I get the error "The column prefix 'contacts' does not match with a table name or alias used in the query".

I am trying to obtain all fields from the communications table whether it is used or NULL.

Many thanks
Jon

SELECT organisations.organisation_number, organisations.contact_number, organisations.name, organisations.address_number,
organisations.std_code, organisations.telephone, organisations.status, contacts.title, contacts.initials, contacts.forenames,
contacts.surname, contacts.contact_number, contacts.label_name, contact_roles.role, addresses.address, addresses.town, addresses.county,
addresses.postcode, addresses.country, communications.device, communications.notes

FROM bmf.dbo.addresses addresses, bmf.dbo.communications communications, bmf.dbo.contact_roles contact_roles, bmf.dbo.contacts contacts,
bmf.dbo.organisations organisations
RIGHT OUTER JOIN communications as comm
ON contacts.contact_number=comm.contact_number

WHERE contact_roles.contact_number = contacts.contact_number AND organisations.address_number = contacts.address_number
AND addresses.address_number = contacts.address_number AND addresses.address_number = organisations.address_number
AND communications.address_number = addresses.address_number AND communications.contact_number = contact_roles.contact_number
AND communications.contact_number = contacts.contact_number AND ((organisations.status='FULL') AND (contact_roles.role='MAIN')
AND (communications.device='EM'))

ORDER BY organisations.name

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-04-22 : 09:18:34
try to use another alias
for example: bmf_contacts


Too old to Rock'n'Roll too young to die.
Go to Top of Page

fuzzyjonclay
Starting Member

9 Posts

Posted - 2014-04-22 : 09:23:54
Thanks for your answer.

However, where would I specify this alias?

I'm new to SQL, so it's all a bit hard for me!!
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-04-22 : 09:29:27
SELECT organisations.organisation_number, organisations.contact_number, organisations.name, organisations.address_number,
organisations.std_code, organisations.telephone, organisations.status, bmf_contacts.title, bmf_contacts.initials, bmf_contacts.forenames,
bmf_contacts.surname, bmf_contacts.contact_number, bmf_contacts.label_name, contact_roles.role, addresses.address, addresses.town, addresses.county,
addresses.postcode, addresses.country, communications.device, communications.notes

FROM bmf.dbo.addresses addresses, bmf.dbo.communications communications, bmf.dbo.contact_roles contact_roles, bmf.dbo.contacts bmf_contacts,
bmf.dbo.organisations organisations
RIGHT OUTER JOIN communications as comm
ON bmf_contacts.contact_number=comm.contact_number

WHERE contact_roles.contact_number = bmf_contacts.contact_number AND organisations.address_number = bmf_contacts.address_number
AND addresses.address_number = bmf_contacts.address_number AND addresses.address_number = organisations.address_number
AND communications.address_number = addresses.address_number AND communications.contact_number = contact_roles.contact_number
AND communications.contact_number = bmf_contacts.contact_number AND ((organisations.status='FULL') AND (contact_roles.role='MAIN')
AND (communications.device='EM'))

ORDER BY organisations.name



Too old to Rock'n'Roll too young to die.
Go to Top of Page

fuzzyjonclay
Starting Member

9 Posts

Posted - 2014-04-22 : 09:33:01
Hi there
Thanks for that. However, I still get the same error.

I've now realised that I need a FULL OUTER JOIN, but I guess this wouldn't make any difference?

The actual problem I have is that I am trying to retrieve e-mail addresses from the communications table even if the column on a particular record is blank (i.e. no email address present), but the report is only showing rows where there IS an email address in the communications table.

Many thanks for your continued help.

Best wishes
Jon
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-04-22 : 09:39:46
Your actual problem isn't the e-mail address.
Your actual problem is the error message.
Are you sure the message is still about prefix 'contacts'?


Too old to Rock'n'Roll too young to die.
Go to Top of Page

fuzzyjonclay
Starting Member

9 Posts

Posted - 2014-04-22 : 09:44:18
Yes, the errors are as follows:-

[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'addresses' does not match with a table name or alias name used in the query.
[Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous column name 'address_number'.
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'contacts' does not match with a table name or alias name used in the query.

This is all very confusing to me as you can imagine!

Go to Top of Page

fuzzyjonclay
Starting Member

9 Posts

Posted - 2014-04-22 : 09:45:52
When I run the following script it all works fine (except for the fact that all records are not getting retrieved, only the ones with an e-mail address in place):-

SELECT organisations.organisation_number, organisations.status, organisations.name,
organisations.std_code, organisations.telephone, contacts.contact_number, contacts.title, contacts.initials, contacts.forenames,
contacts.surname, contacts.label_name, contact_roles.role, addresses.address, addresses.town, addresses.county,
addresses.postcode, addresses.country, communications.device, communications.notes

FROM bmf.dbo.addresses addresses, bmf.dbo.communications communications, bmf.dbo.contact_roles contact_roles, bmf.dbo.contacts contacts,
bmf.dbo.organisations organisations


WHERE contact_roles.contact_number = contacts.contact_number AND organisations.address_number = contacts.address_number
AND addresses.address_number = contacts.address_number AND addresses.address_number = organisations.address_number
AND communications.address_number = addresses.address_number AND communications.contact_number = contact_roles.contact_number
AND communications.contact_number = contacts.contact_number AND ((organisations.status='FULL') AND (contact_roles.role='MAIN')
AND (communications.device='EM'))

ORDER BY organisations.name
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-04-22 : 10:03:52
not very sure without table structure, sample data and wanted result but maybe this:

SELECT organisations.organisation_number, organisations.status, organisations.name, 
organisations.std_code, organisations.telephone, contacts.contact_number, contacts.title, contacts.initials, contacts.forenames,
contacts.surname, contacts.label_name, contact_roles.role, addresses.address, addresses.town, addresses.county,
addresses.postcode, addresses.country, communications.device, communications.notes

FROM bmf.dbo.addresses addresses

left join bmf.dbo.communications communications on communications.address_number = addresses.address_number

join bmf.dbo.contacts contacts on addresses.address_number = contacts.address_number

join bmf.dbo.contact_roles contact_roles on contact_roles.contact_number = contacts.contact_number

join bmf.dbo.organisations organisations on addresses.address_number = organisations.address_number


WHERE ((organisations.status='FULL') AND (contact_roles.role='MAIN') AND (communications.device='EM' or communications.device is null))

ORDER BY organisations.name



Too old to Rock'n'Roll too young to die.
Go to Top of Page

fuzzyjonclay
Starting Member

9 Posts

Posted - 2014-04-22 : 10:54:56
Thank you :)
Go to Top of Page
   

- Advertisement -