|
|
Posted by Artie on 10/24/07 01:16
I did not post the full DDL because there are so many fields that are not
relevant and felt it would complicate things. It would open up a can of
worms as to why it is desgined the way it is (I did not design it).
Erland's response did the trick.
Do you guys (or gals) have a preferred method of generating insert
statements that pull data from the table? I have used sp_generate_inserts
from http://vyaskn.tripod.com but have run into some cases where the 8000
char limit was not enough.
I really appreciate the help all of you provide on these boards to newbs
like me.
"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1193185951.805397.62450@v23g2000prn.googlegroups.com...
> 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.
>
Navigation:
[Reply to this message]
|