[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Creative solution to relatively simply requirement

Rob

3/28/2007 8:36:00 AM

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.


5 Answers

Uri Dimant

3/28/2007 9:01:00 AM

0

Rob

For inserts

SELECT * FROM Features WHERE
NOT EXISTS (SELECT * FROM PubFeatures WHERE Features.FeatureID =PubFeatures
..PubFeatureID)

For deletion


SELECT * FROM PubFeatures WHERE
NOT EXISTS (SELECT * FROM Features WHERE Features.FeatureID =PubFeatures
..PubFeatureID)


Consider creating ON DELETE CASCADE feature to delete the data from
PubFeatures automatically where user delets the data from Features







"Rob" <rob_nicholson@nospam_unforgettable.com> wrote in message
news:CNpOh.27104$267.3417@newsfe6-gui.ntli.net...
> 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.
>


Rob

3/29/2007 9:12:00 AM

0

> SELECT * FROM Features WHERE
> NOT EXISTS (SELECT * FROM PubFeatures WHERE Features.FeatureID
> =PubFeatures .PubFeatureID)

Ahh thanks for that - that NOT EXISTS is the key bit - I'll have a play
later.

I've always steered away from things like ON DELETE CASCADE and triggers
etc. as always have to keep an eye on portability for some projects. But
this is always going to be MS-SQL. How SQL standard is ON DELETE CASCADE?

Cheers, Rob.


Uri Dimant

3/29/2007 9:30:00 AM

0

Rob

Yes , ON DELETE CASCADE command may cause to locking or blocking as it
scans the table for rows to be deleted.
But as you know it depends and you have to test it.




"Rob" <rob_nicholson@nospam_unforgettable.com> wrote in message
news:bpLOh.18398$5c2.4649@newsfe3-win.ntli.net...
>> SELECT * FROM Features WHERE
>> NOT EXISTS (SELECT * FROM PubFeatures WHERE Features.FeatureID
>> =PubFeatures .PubFeatureID)
>
> Ahh thanks for that - that NOT EXISTS is the key bit - I'll have a play
> later.
>
> I've always steered away from things like ON DELETE CASCADE and triggers
> etc. as always have to keep an eye on portability for some projects. But
> this is always going to be MS-SQL. How SQL standard is ON DELETE CASCADE?
>
> Cheers, Rob.
>


Tibor Karaszi

3/29/2007 10:17:00 AM

0

> How SQL standard is ON DELETE CASCADE?

It is ANSI SQL since, I believe, at least -92.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://www.solidqualitylea...


"Rob" <rob_nicholson@nospam_unforgettable.com> wrote in message
news:bpLOh.18398$5c2.4649@newsfe3-win.ntli.net...
>> SELECT * FROM Features WHERE
>> NOT EXISTS (SELECT * FROM PubFeatures WHERE Features.FeatureID =PubFeatures .PubFeatureID)
>
> Ahh thanks for that - that NOT EXISTS is the key bit - I'll have a play later.
>
> I've always steered away from things like ON DELETE CASCADE and triggers etc. as always have to
> keep an eye on portability for some projects. But this is always going to be MS-SQL. How SQL
> standard is ON DELETE CASCADE?
>
> Cheers, Rob.
>


Rob

3/29/2007 8:12:00 PM

0

> SELECT * FROM Features WHERE
> NOT EXISTS (SELECT * FROM PubFeatures WHERE Features.FeatureID
> =PubFeatures .PubFeatureID)

I've cracked this with your suggestion... your NOT EXISTS got me on the
right track. A bit more was required as what's needed is the Pub ID as well
as the missing Symbol IDs.

Select
P.[PubID], F.[FeatureID]
From [Pubs] P, [Features] F
Where Not Exists
(Select * From [PubFeatures] PF Where F.[FeatureID]=PF.[FeatureID] And
P.[PubID]=PS.[PubID])

The key addition is the From Table1, Table2 format which returns a list of
pubs with a list of possible features for each pub. This is then fed into
the Not Exists you suggested.

The output is something like this:

PubID, FeatureID
32,34
32,35
33,34

Which means that Pub ID=32 needs new features adding but Pub ID=33 only
needs one.

This select can be then fed directly into an INSERT command.

It's cut down execution in the test database (which only had 250 pubs) from
15 seconds to almost instantaneous.

Cheers, Rob.