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.
Author |
Topic |
fuzzyjonclay
Starting Member
9 Posts |
Posted - 2014-04-22 : 08:08:26
|
Hi thereI 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 thanksJonSELECT 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.notesFROM bmf.dbo.addresses addresses, bmf.dbo.communications communications, bmf.dbo.contact_roles contact_roles, bmf.dbo.contacts contacts, bmf.dbo.organisations organisationsRIGHT OUTER JOIN communications as commON contacts.contact_number=comm.contact_numberWHERE 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 aliasfor example: bmf_contacts Too old to Rock'n'Roll too young to die. |
|
|
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!! |
|
|
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.notesFROM bmf.dbo.addresses addresses, bmf.dbo.communications communications, bmf.dbo.contact_roles contact_roles, bmf.dbo.contacts bmf_contacts, bmf.dbo.organisations organisationsRIGHT OUTER JOIN communications as commON bmf_contacts.contact_number=comm.contact_numberWHERE 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. |
|
|
fuzzyjonclay
Starting Member
9 Posts |
Posted - 2014-04-22 : 09:33:01
|
Hi thereThanks 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 wishesJon |
|
|
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. |
|
|
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! |
|
|
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.notesFROM bmf.dbo.addresses addresses, bmf.dbo.communications communications, bmf.dbo.contact_roles contact_roles, bmf.dbo.contacts contacts, bmf.dbo.organisations organisationsWHERE 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 : 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.notesFROM bmf.dbo.addresses addressesleft join bmf.dbo.communications communications on communications.address_number = addresses.address_numberjoin bmf.dbo.contacts contacts on addresses.address_number = contacts.address_numberjoin bmf.dbo.contact_roles contact_roles on contact_roles.contact_number = contacts.contact_numberjoin 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. |
|
|
fuzzyjonclay
Starting Member
9 Posts |
Posted - 2014-04-22 : 10:54:56
|
Thank you :) |
|
|
|
|
|
|
|