Posted by creedp.71 on 11/28/07 00:53

On 25 Oct, 21:48, Ed Murphy <emurph...@socal.rr.com> wrote:
> --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.

I know this thread is month old but I couldn't help but renew it. It
warms my heart to see a couple people that know how to use an NOT
EXISTS clause with a correlated subquery properly, especially when so
many people don't understand the impact of the seemingly simple "NOT
IN()" version which would use a nested sub-select instead leaving an
exponentialy larger footprint on the DB.
However, unless I am mistaken, this is a classic case of exactly the
type of scenario that forced me to switch from the old-school Oracle
SQL+ syntax (pre v8) and start using the ANSI SQL syntax that seems
far too "wordy" just to join a couple tables together. It was a
scenario just like this that I realized you could only do with the
newer syntax. And (depending on the data of course) it may be a more
efficient query than the correlated subquery.

The basic idea is just change the join to an outer join, and then move
the additional criteria into the JOIN clause just like it's another
column you're joining to. Then the only criteria in the where clause
will be a test for NULL in the primary key column of the joining table
which would mean there's not a matching record to the join. This
gives you the ability to evaluate criteria on the records both before
and after the join is made. This is also a common method used in ETL
when writing an "insert into select from" statement in a single
statement that will give you a single DML statement that both tests
for the existence of a record in a table before attempting to insert a
batch of rows into it.

Consider this alternative and let me know if I screw this one up,
after all, it's getting late, and I'm still at work :) .....

SELECT company.code, company.name, company.type,
contacts.fullname
FROM company Left JOIN
contacts ON company.code = contacts.code
AND (company.type in ('C', 'R')) and (contacts.fullname =
'Accounting')
WHERE contacts.code IS NULL
order by company.code

[Back to original message]