Query problem

I have the following tables in a mailng list management program:
CREATE TABLE [dbo].[tabMailGroupSubscribers] (
[MailSubscriberID] [uniqueidentifier] NOT NULL ,
[MailGroupID] [uniqueidentifier] NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tabMailGroups] (
[MailGroupID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[GroupName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[MemberOf] [uniqueidentifier] NULL ,
[GroupEmailAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Description] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Conditions] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[DirectLink] [bit] NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tabMailSubscribers] (
[MailSubscriberID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[HotelID] [int] NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Surname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Designation] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[EmailAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO
Problem:
How can I dispaly a list containing all the mailgroups in tabMailgroups, and
have the ability to see if a subscriber from tabMailSubscribers has
subscribed to a specific group, and wich not?
Something like this...
CREAT PROCEDURE dbo. proc_tabMailGroupSubscribers_MailSubscri
berID
@.MailSubscriberID varchar(50)
AS
Begin
SELECT tabMailGroups.MailGroupID, tabMailGroups.GroupName,
tabMailGroupSubscribers.MailSubscriberID, tabMailGroups.GroupEmailAddress,
tabMailGroups.Description, tabMailGroups.Conditions, (CASE WHEN
tabMailGroupSubscribers.MailSubscriberID IS NULL THEN 0 ELSE 1 END)
AS Subscribed
FROM tabMailGroups LEFT OUTER JOIN
tabMailGroupSubscribers ON tabMailGroups.MailGroupID =
tabMailGroupSubscribers.MailGroupID
WHERE (tabMailGroupSubscribers.MailSubscriberID = @.MailSubscriberID)
end
Return
Only problem is this is not working.
Thanks for any helpUse [tabMailGroupSubscribers].[MailGroupID] in the CASE expression.
...
(
CASE
WHEN [tabMailGroupSubscribers].[MailGroupID] IS NULL THEN 0
ELSE 1
END
) AS Subscribed
...
AMB
"HB" wrote:

> I have the following tables in a mailng list management program:
> CREATE TABLE [dbo].[tabMailGroupSubscribers] (
> [MailSubscriberID] [uniqueidentifier] NOT NULL ,
> [MailGroupID] [uniqueidentifier] NOT NULL ,
> [UpdatedBy] [int] NOT NULL ,
> [DateUpdated] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tabMailGroups] (
> [MailGroupID] uniqueidentifier ROWGUIDCOL NOT NULL ,
> [GroupName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [MemberOf] [uniqueidentifier] NULL ,
> [GroupEmailAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Description] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Conditions] [ntext] COLLATE Latin1_General_CI_AS NULL ,
> [DirectLink] [bit] NOT NULL ,
> [UpdatedBy] [int] NOT NULL ,
> [DateUpdated] [datetime] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tabMailSubscribers] (
> [MailSubscriberID] uniqueidentifier ROWGUIDCOL NOT NULL ,
> [HotelID] [int] NOT NULL ,
> [Name] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Surname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Designation] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [EmailAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [UpdatedBy] [int] NOT NULL ,
> [DateUpdated] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> Problem:
> How can I dispaly a list containing all the mailgroups in tabMailgroups, a
nd
> have the ability to see if a subscriber from tabMailSubscribers has
> subscribed to a specific group, and wich not?
> Something like this...
> CREAT PROCEDURE dbo. proc_tabMailGroupSubscribers_MailSubscri
berID
> @.MailSubscriberID varchar(50)
> AS
> Begin
> SELECT tabMailGroups.MailGroupID, tabMailGroups.GroupName,
> tabMailGroupSubscribers.MailSubscriberID, tabMailGroups.GroupEmailAddress,
> tabMailGroups.Description, tabMailGroups.Conditions, (CASE WHEN
> tabMailGroupSubscribers.MailSubscriberID IS NULL THEN 0 ELSE 1 END)
> AS Subscribed
> FROM tabMailGroups LEFT OUTER JOIN
> tabMailGroupSubscribers ON tabMailGroups.MailGroupID =
> tabMailGroupSubscribers.MailGroupID
> WHERE (tabMailGroupSubscribers.MailSubscriberID = @.MailSubscriberID)
> end
> Return
> Only problem is this is not working.
>
> Thanks for any help
>
>|||Hi AMB
This does not seem to do the trick.
It is still not returning the correct results. There is records outstanding
from the results.
Regards
Hennie
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:F1B1C8B2-62E6-4AF7-821A-F6DE740C0D10@.microsoft.com...
> Use [tabMailGroupSubscribers].[MailGroupID] in the CASE expression.
> ...
> (
> CASE
> WHEN [tabMailGroupSubscribers].[MailGroupID] IS NULL THEN 0
> ELSE 1
> END
> ) AS Subscribed
> ...
>
> AMB
> "HB" wrote:
>

Query problem

I have the following tables in a mailng list management program:
CREATE TABLE [dbo].[tabMailGroupSubscribers] (
[MailSubscriberID] [uniqueidentifier] NOT NULL ,
[MailGroupID] [uniqueidentifier] NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tabMailGroups] (
[MailGroupID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[GroupName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[MemberOf] [uniqueidentifier] NULL ,
[GroupEmailAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Description] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Conditions] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[DirectLink] [bit] NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tabMailSubscribers] (
[MailSubscriberID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[HotelID] [int] NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Surname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Designation] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[EmailAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[UpdatedBy] [int] NOT NULL ,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO
Problem:
How can I dispaly a list containing all the mailgroups in tabMailgroups, and
have the ability to see if a subscriber from tabMailSubscribers has
subscribed to a specific group, and wich not?
Something like this...
CREAT PROCEDURE dbo. proc_tabMailGroupSubscribers_MailSubscri
berID
@.MailSubscriberID varchar(50)
AS
Begin
SELECT tabMailGroups.MailGroupID, tabMailGroups.GroupName,
tabMailGroupSubscribers.MailSubscriberID, tabMailGroups.GroupEmailAddress,
tabMailGroups.Description, tabMailGroups.Conditions, (CASE WHEN
tabMailGroupSubscribers.MailSubscriberID IS NULL THEN 0 ELSE 1 END)
AS Subscribed
FROM tabMailGroups LEFT OUTER JOIN
tabMailGroupSubscribers ON tabMailGroups.MailGroupID =
tabMailGroupSubscribers.MailGroupID
WHERE (tabMailGroupSubscribers.MailSubscriberID = @.MailSubscriberID)
end
Return
Only problem is this is not working.
Should I rateher change my table design to do this sort query, I'm not sure.
Thanks for any helpThe problem is that your where clause is making your LEFT OUTER JOIN
behave as an INNER JOIN. Try something like this:
CREATE PROCEDURE dbo. proc_tabMailGroupSubscribers_MailSubscri
berID
@.MailSubscriberID varchar(50)
AS
Begin
SELECT tabMailGroups.MailGroupID, tabMailGroups.GroupName,
tabMailGroupSubscribers.MailSubscriberID,
tabMailGroups.GroupEmailAddress,
tabMailGroups.Description, tabMailGroups.Conditions, (CASE WHEN
tabMailGroupSubscribers.MailSubscriberID IS NULL THEN 0 ELSE 1 END)
AS Subscribed
FROM tabMailGroups LEFT OUTER JOIN
tabMailGroupSubscribers ON tabMailGroups.MailGroupID =
tabMailGroupSubscribers.MailGroupID
AND tabMailGroupSubscribers.MailSubscriberID = @.MailSubscriberID
end
Return
Regards,
William D. Bartholomew
http://blog.bartholomew.id.au
*** Sent via Developersdex http://www.examnotes.net ***|||Hi William, thanks, it is working great.
Hennie
"William Bartholomew" <william(at)bartholomew[dot]id{dot}au> wrote in
message news:#RoA3QzOFHA.1564@.TK2MSFTNGP14.phx.gbl...
> The problem is that your where clause is making your LEFT OUTER JOIN
> behave as an INNER JOIN. Try something like this:
> CREATE PROCEDURE dbo. proc_tabMailGroupSubscribers_MailSubscri
berID
> @.MailSubscriberID varchar(50)
> AS
> Begin
> SELECT tabMailGroups.MailGroupID, tabMailGroups.GroupName,
> tabMailGroupSubscribers.MailSubscriberID,
> tabMailGroups.GroupEmailAddress,
> tabMailGroups.Description, tabMailGroups.Conditions, (CASE WHEN
> tabMailGroupSubscribers.MailSubscriberID IS NULL THEN 0 ELSE 1 END)
> AS Subscribed
> FROM tabMailGroups LEFT OUTER JOIN
> tabMailGroupSubscribers ON tabMailGroups.MailGroupID =
> tabMailGroupSubscribers.MailGroupID
> AND tabMailGroupSubscribers.MailSubscriberID = @.MailSubscriberID
> end
> Return
>
> Regards,
> William D. Bartholomew
> http://blog.bartholomew.id.au
> *** Sent via Developersdex http://www.examnotes.net ***