[wdvltalk] [SQL] SELECT COUNT over multiple tables

[Howard Cheng]

I've got two tables in my DB where a certain value may exist and I want to 
get a count of how many there are total. Anyone know an easy way to do 
this? The application is a CMS and the operation in question is a user 
deletion. I want to query two tables to see if the user has created or 
edited content, or has uploaded files to the resource gallery. So I've got 
tables like:

CREATE TABLE Content AS (
    contentID INT IDENTITY PRIMARY KEY,
    authorID INT,
    editorID INT,
    [more columns]
)
CREATE TABLE Resources AS (
    resourceID INT IDENTITY PRIMARY KEY,
    authorID INT,
    [more columns]
)
CREATE TABLE Users AS (
    userID INT IDENTITY PRIMARY KEY,
    [more columns]
)
... and foreign key constraints on three columns: Content.authorID, 
Content.editorID, and Resources.authorID.

So I want to retrieve the number of records where the userID is an authorID 
or an editorID. If it's > 0 then throw an exception and prevent deletion. 
But I can't seem to come up with a good query.

Anyone? Rudy? :)

Of course I suppose I could just do a delete and catch any SqlExceptions 
that come my way.

TIA.

::::::::::::::::::::::::::::::::::
Howard Cheng
http://www.howcheng.com/
howcheng at ix dot netcom dot com
AIM: bennyphoebe
ICQ: 47319315 

____ � The WDVL Discussion List from WDVL.COM � ____
To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] 
      Send Your Posts To: [EMAIL PROTECTED]
To change subscription settings to the wdvltalk digest version:
   http://wdvl.internet.com/WDVL/Forum/#sub

________________  http://www.wdvl.com  _______________________

You are currently subscribed to wdvltalk as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]