Creative solution to relatively simply requirement

microsoft.public.sqlserver.programming

28 Mar 2007 8:35 AM

Rob

I've always wondered if there is an elegant solution in T-SQL to the
following requirement.

Consider a database consisting of three tables:

Pubs: Table of pubs, e.g. "Red Lion"
PubID: Unique ID
Name: Name of the pub
Features: Table of features, e.g "Real fire" or "Disabled access"
FeatureID: Unique ID
Name: Name of the feature, e.g. "Real fire"
PubFeatures: Instance table of features at the pub
PubFeatureID: Unique ID
PubID: Foreign key to the parent pub
FeatureID: Link to Features table
Available: Yes/No whether feature is available

So if there are 10 pub records and 5 features, then each pub would have 5
child records in PubFeatures. A SELECT query to read back the information
would look something like this:

SELECT
    Pubs.Name, Features.Name, PubFeatures.Availability
FROM Pubs
INNER JOIN PubFeatures
    INNER JOIN Features
    ON PubFeatures.FeatureID=Features.FeatureID
ON Pubs.PubID=PubFeatures.PubID

The requirement is to write a FixupPubFeatures stored procedure which is
executed everytime a addition or deletion is made from the Features table.
The stored procedure would identify which pubs need additional PubFeature
records added (when a new feature is added) or need PubFeatures records
deleting (when a feature is deleted).

Currently I loop through each pub one by one. One INSERT is run per pub
using a nested SELECT to determine which PubFeatures need adding. A second
DELETE with nested SELECT is then run to delete PubFeatures.

What I'd like to do is get rid of the outer loop and somehow construct one
big INSERT and then DELETE to do all pubs at once. I feel like it's
something to do with FULL OUTER JOINS but I've not been able to get it
working. The problem I think is that in SQL will have to sort of "make up
records" for the missing PubFeatures which maybe is asking too much.

So a challenge :-)

Cheers, Rob.