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:
>