Conditionally filter recordset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Conditionally filter recordset

Author

Topic

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-03-19 : 12:43:11

Ok, my goal is to either return all records in table A or only records in table A that do not have related records in table B. I want to do this while keeping the performance of the query as efficient as possible. Here are the basic table structures:

CREATE TABLE dbo.[User]
(
InternalKey INT IDENTITY (1, 1) NOT NULL,
UserTypeKey INT NOT NULL,
ClientKey INT NULL,
VendorKey INT NULL,
FirstName NVARCHAR (30) NOT NULL CONSTRAINT DF_User_FirstName DEFAULT (''),
MiddleName NVARCHAR (15) NOT NULL CONSTRAINT DF_User_MiddleName DEFAULT (''),
LastName NVARCHAR (30) NOT NULL CONSTRAINT DF_User_LastName DEFAULT ('')
)

CREATE TABLE dbo.NotificationGroupMember
(
InternalKey INT IDENTITY (1, 1) NOT NULL,
NotificationGroupKey INT NOT NULL,
Email NVARCHAR(100) NOT NULL,
DisplayName NVARCHAR(100) NOT NULL,
NonSystemUser BIT NOT NULL,
UserKey INT NULL,
SendTo bit NOT NULL CONSTRAINT DF_NotificationMember_SendTo DEFAULT (1),
CarbonCopy bit NOT NULL CONSTRAINT DF_NotificationMember_CarbonCopy DEFAULT (0),
BlindCarbonCopy bit NOT NULL CONSTRAINT DF_NotificationMember_BlindCarbonCopy DEFAULT (0)
)

Basically, I'll have a procedure that has a parameter named @ExcludeExisting. If it's 0, I simply want to return all the records from the User table and the related data for matching records in NotificationGroupMember. If it's 1 I only want to return records from the User table that DO NOT have a matching record in NotificationGroupMember.

Any tips?

Edited by - tfountain on 03/19/2003 12:44:24

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-19 : 13:08:39

something like:

select u.*,n*
from
user U
left outer join
NotificationGroupMember N
on
u.internalkey = n.userkey
where
((@ExcludeListing <> 0) or (n.internalkey is null)) or
((@ExcludeListing <> 1) or (n.interkeykey is not null))

not sure about your relationship between the two tables, check the join clause.

Note:

(Not A) or (B) <=> if A then B

- Jeff

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-03-19 : 13:28:06

I tried something like that but I'm not happy with the query plan. But I guess it's as good as it's going to get. My exact query is like so:

SELECT <columns>
FROM <database>.dbo.[User] AS U
LEFT JOIN <database>.dbo.NotificationGroupMember AS NGM
ON U.InternalKey = NGM.UserKey
AND NGM.NotificationGroupKey = @NotificationGroupKey
WHERE (NGM.InternalKey IS NULL AND @ExcludeExisting = 1)
OR (@ExcludeExisting = 0)

Thanks for your input.

HendersonToo
Starting Member

20 Posts

Posted - 2004-02-02 : 11:45:56

Well I lost my password and my old email address, so here I start fresh ;)

What I do in these situations, because you have already opted to place this in a sp, is write the query twice, then switch the SELECT you run on your @ExcludeExisting parameter.

Yes its anithetical to normal implementation practices, but this is T-SQL! Its declarative, and you can't always afford to be concise in your expression. Dump the @ExcludeExisting from the SELECT statement. As a bonus, the compiled SP, will have nice query plans ready to go for both cases.

Don't forget to document why its written twice.
(Or write two SPs and name them informatively =)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources