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