Help with SQL Query

[George Durzi]

I have a table called AppUser as defined below

CREATE TABLE [AppUser] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[ParentId] [int] NULL ,
CONSTRAINT [PK_AppUser] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_AppUser_AppUser] FOREIGN KEY
(
[ParentId]
) REFERENCES [AppUser] (
[Id]
)
) ON [PRIMARY]
GO

Some sample data:
INSERT INTO AppUser (ParentId) VALUES (NULL)
INSERT INTO AppUser (ParentId) VALUES (1)

A simple products table:

CREATE TABLE [dbo].[Products] (
[Id] [int] NOT NULL
) ON [PRIMARY]
GO

Some sample data:
INSERT INTO Products (Id) Values (1)
INSERT INTO Products (Id) Values (2)

Finally, a table called AppUserProducts, where the only AppUsers who have
records in this table are the ones with a ParentId of NULL

CREATE TABLE [dbo].[AppUserProducts] (
[ProductId] [int] NOT NULL ,
[AppUserId] [int] NOT NULL
) ON [PRIMARY]
GO

Some sample data:
INSERT INTO [AppUserProducts]([ProductId], [AppUserId]) VALUES(1,1)
INSERT INTO [AppUserProducts]([ProductId], [AppUserId]) VALUES(2,1)

I'd like to build a query that returns this data set:

ProductId AppUserId ParentId
1 1 NULL
2 1 NULL
1 2 1
2 2 1
Thanks for your help

George

[George Durzi]

OOOPS, wrong group

"George Durzi" <gd****@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

I have a table called AppUser as defined below

CREATE TABLE [AppUser] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[ParentId] [int] NULL ,
CONSTRAINT [PK_AppUser] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_AppUser_AppUser] FOREIGN KEY
(
[ParentId]
) REFERENCES [AppUser] (
[Id]
)
) ON [PRIMARY]
GO

Some sample data:
INSERT INTO AppUser (ParentId) VALUES (NULL)
INSERT INTO AppUser (ParentId) VALUES (1)

A simple products table:

CREATE TABLE [dbo].[Products] (
[Id] [int] NOT NULL
) ON [PRIMARY]
GO

Some sample data:
INSERT INTO Products (Id) Values (1)
INSERT INTO Products (Id) Values (2)

Finally, a table called AppUserProducts, where the only AppUsers who have
records in this table are the ones with a ParentId of NULL

CREATE TABLE [dbo].[AppUserProducts] (
[ProductId] [int] NOT NULL ,
[AppUserId] [int] NOT NULL
) ON [PRIMARY]
GO

Some sample data:
INSERT INTO [AppUserProducts]([ProductId], [AppUserId]) VALUES(1,1)
INSERT INTO [AppUserProducts]([ProductId], [AppUserId]) VALUES(2,1)

I'd like to build a query that returns this data set:

ProductId AppUserId ParentId
1 1 NULL
2 1 NULL
1 2 1
2 2 1
Thanks for your help

George