Posted by --CELKO-- on 10/24/07 00:32

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Sample data is also a good idea,
along with clear specifications. It is very hard to debug code when
you do not let us see it.

>> I need to find accounts that do not contain a contact person called 'Accounting'. Each account may contain multiple contacts.<<

What is the key of the Companies table (your singular name means that
you have one or fewer rows in that table, but I assume this is part of
the other violations of ISO-11179 with uselessly vague data element
names like "name" (of my dog?) "code" (ZIP code?) and "type" (blood
type?).

It sorta looks like code is the key, but that makes no sense. BY
DEFINITION a code of any kind cannot be a key; this is fundamental.

You need a DUNS number or other industry standard company identifier.
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.

Here is a weird way to do this, based on guessing at your DDL:

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.

[Back to original message]