bridge table NULL query--please help

[Xenophobe]

I'm stuck trying to solve the following NULL query. I know why the query
isn't working, but am uncertain on how to solve it.

There are two tables, ServiceTypes and ServiceCompanies:

Table: ServiceTypes
Columns: ServiceTypeID, ServiceTypeName

Table: ServiceCompanies
Columns: ServiceTypeID, CompanyID

ServiceTypes and CompanyID record combinations must be unique. In other
words a company is only allowed to have one of each service type associated
with it.

ServiceTypes contains 5 different service types:

ServiceTypeID, ServiceTypeName
1, Plumbing
2, Electrical
3, Flooring
4, Drywall
5, Landscaping

A single company can offer 1 or more services. These are stored in
ServiceCompanies bridge table:

ServiceTypeID, CompanyID
1, 1
2, 1
3, 1
1, 2
2, 2

....and so on.

So here's my challenge (finally!) I would like to return a recordset of
services that are NOT currently associated with a company. This would allow
them to add any additional services that aren't already defined.

I have played with many variations of the following query and read the docs,
but without success.

SELECT ST.ServiceTypeID FROM ServiceTypes ST
LEFT JOIN ServiceCompanies SC ON ST.ServiceTypeID = SC.ServiceTypeID
WHERE SC.ServiceTypeID IS NULL and SC.CompanyID = ?

No matter what, I always get zero results. The reason for this seems to be
that the query finds other references to ServiceTypeID in ServiceCompanies
and is ignoring the CompanyID specific portion of the WHERE clause.

I have been unable to find the right syntax. Any suggestions would be
greatly appreciated!

 

[Tom Ellison]

Dear Xeno:

SELECT C.CompanyID, T.ServiceTypeID
FROM (SELECT DISTINCT CompanyID FROM ServiceCompanies) C,
ServiceTypes T

The above gives the matrix of all possibilities. It is disappointing
you don't have a separate table of Companies with a unique CompanyID,
but this can be synthesized with the subquery above:

SELECT DISTINCT CompanyID FROM ServiceCompanies

Now, all you need to is eliminate those company/types that already
exist:

SELECT C.CompanyID, T.ServiceTypeID
FROM (SELECT DISTINCT CompanyID FROM ServiceCompanies) C,
ServiceTypes T
WHERE NOT EXISTS(SELECT * FROM ServiceCompanies C1
WHERE C1.CompanyID = C.CompanyID
AND C1.ServiceTypeID = C.ServiceTypeID)

I think that should do it.

This could also be done with a left join to the ServiceCompany table,
filtering for nulls in the intersection. That might be faster when
using Jet. We can develop that later if necessary.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Xenophobe]

Hi Tom,

I tried your query "as is" and it prompts me for a "C.ServiceTypeID". This
appears to be a reference to the last WHERE clause, as if the coloration
name isn't properly defined.

I studied your query example and don't see where a specific CompanyID is
defined.

There is a separate table with unique CompanyID's, aptly named Companies.
Would using this in the query simplify things?

As per your last comment, I'm also interested in learning how this query
could be accomplished using LEFT JOIN and filtering NULLS. This technique
would come in handy elsewhere.

Thanks in advance for your reply.

 

[Tom Ellison]

Dear Xeno:

I'll try again. I see a mistake I made, and I know a bit more about
what you want.

Let's to this:

SELECT C.CompanyID, T.ServiceTypeID
FROM Companies C, ServiceTypes T
WHERE NOT EXISTS(SELECT * FROM ServiceCompanies SC
WHERE SC.CompanyID = C.CompanyID
AND SC.ServiceTypeID = T.ServiceTypeID)

There's a tiny change in the last line. The alias on the right side
of the equals was to the wrong table. I also changed from using your
junction table to supply the companies to using the whole companies
table. This will now include companies that have NO service types,
which it couldn't have picked up from the junction table.

For the LEFT JOIN method:

SELECT C.CompanyID, T.ServiceTypeID
FROM Companies C, ServiceTypes T
LEFT JOIN ServiceCompanies SC
ON SC.CompanyID = C.CompanyID
AND SC.ServiceTypeID = T.ServiceTypeID
WHERE SC.CompanyID IS NULL

Any better?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Xenophobe]

Tom, you're my hero!

The LEFT JOIN works perfectly. I simply added "C.CompanyID = " to the WHERE
which allows me to get results for one specific company.

I always like to learn something from these experiences so the key to this
solution is the inclusion of a table with unique IDs, right?

Again, thanks so much.

Xeno

 

[Tom Ellison]

Dear Xeno:

Well, the "key" would be whatever it was you hadn't thought of. That
makes it a kind of a personal thing.

In my first post, I tried to illustrate the cross-product that
produces all combinations of company with service type. Creating the
cross-product is the first key. Then you eliminate the combinations
that already exist. Both those steps are key to the solution.

Glad it worked out for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts