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

Query Linked Server?

Using SQL 2005 Management Studio to access a SQL 2000 Database.
While in the SQL 2000 Database have created a Link server so in Object
explorer it shows a folder called Link Server\DataServer1. I am unable to
query it.
Using windows authentication. Not sure how to query DB from SQL2000. Do
you have to also include anything special like user name and password?
Not able to get the Linkserver control to work.
thanksAre you able to view the objects of the linked server in the object explorer
.
f so, then you can use a 4 part name to access the tables.
Hope this helps.
--
"Harry" wrote:

> Using SQL 2005 Management Studio to access a SQL 2000 Database.
> While in the SQL 2000 Database have created a Link server so in Object
> explorer it shows a folder called Link Server\DataServer1. I am unable to
> query it.
> Using windows authentication. Not sure how to query DB from SQL2000. Do
> you have to also include anything special like user name and password?
> Not able to get the Linkserver control to work.
> thanks
>|||i can see the Linked server in Object explorer. i recall a code that used
open data source to try and query data from it. i am not able to find it
anymore.
Still not working.
If I am directly on the SQL 2000 server and run my query to the ODBC Linked
server it works fine.
Not sure how to resolve the connection issue when I am using 2005 management
studio to connect to the SQL 2000 database and from there query data from th
e
linked server so I can write back to the SQL 2000...ahh I am !
--
Harry
"Omnibuzz" wrote:
> Are you able to view the objects of the linked server in the object explor
er.
> f so, then you can use a 4 part name to access the tables.
> Hope this helps.
> --
>
>
> "Harry" wrote:
>