You are here: Re: Not Exists joining 2 tables « MsSQL Server « IT news, forums, messages
Re: Not Exists joining 2 tables

Posted by Ed Murphy on 10/26/07 03:48

--CELKO-- wrote:

> Your spec asked only for for the companies without a contact =
> 'Accounting'; but we have no idea if the Contacts table (properly
> named!) has a reference to the companies.

Yes, we do. "It sorta looks like code is the key", by your own
admission, and his "companies with a contact = 'Accounting')"
sample query backs this up:

FROM company INNER JOIN
contacts ON company.code = contacts.code

Granted, "'code' is a bad name for a key column" is a valid complaint.

> SELECT CT.company_name
> FROM Contacts AS CT
> GROUP BY CT.company_name
> HAVING SUM(CASE WHEN CT.contact_name = 'Accounting' THEN 1 ELSE 0
> END)= 0;
>
> This is untested; if we had DDL, we could try it!! I assumed that
> Contacts ought to be a relationship between a company and a lawful
> person or role within the company.

You mean a many-to-many linking table between Companies and Persons
(i.e. a person might be a contact for multiple companies)? Okay, but
I still don't see why you would get company_name from any table other
than Companies. Basic normalization.

If you do get company_name from Companies, then you might have a company
with no contacts at all. You could use LEFT JOIN and COALESCE(SUM()),
but Erland's NOT EXISTS is more natural.

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация