Create/Insert scripts:
CREATE TABLE [Product] (
[ProductID] [int] NOT NULL ,
[Name] [char] (10) COLLATE Compatibility_52_409_30003 NULL ,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProductID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Project] (
[ProjectID] [int] NOT NULL ,
[Name] [char] (10) COLLATE Compatibility_52_409_30003 NULL ,
CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED
(
[ProjectID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [ProjectProduct] (
[ProjectID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
CONSTRAINT [PK_ProjectProduct] PRIMARY KEY CLUSTERED
(
[ProjectID],
[ProductID]
) ON [PRIMARY] ,
CONSTRAINT [FK_ProjectProduct_Product] FOREIGN KEY
(
[ProductID]
) REFERENCES [Product] (
[ProductID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_ProjectProduct_Project] FOREIGN KEY
(
[ProjectID]
) REFERENCES [Project] (
[ProjectID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [SubledgerID] (
[SubledgerID] [int] NOT NULL ,
[Name] [char] (10) COLLATE Compatibility_52_409_30003 NULL ,
CONSTRAINT [PK_SubledgerID] PRIMARY KEY CLUSTERED
(
[SubledgerID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [SubledgerProduct] (
[SubledgerID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
CONSTRAINT [PK_SubledgerProduct] PRIMARY KEY CLUSTERED
(
[SubledgerID],
[ProductID]
) ON [PRIMARY] ,
CONSTRAINT [FK_SubledgerProduct_Product] FOREIGN KEY
(
[ProductID]
) REFERENCES [Product] (
[ProductID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_SubledgerProduct_SubledgerID] FOREIGN KEY
(
[SubledgerID]
) REFERENCES [SubledgerID] (
[SubledgerID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
INSERT INTO [Product] ([ProductID], [Name]) VALUES (1, 'Product A')
INSERT INTO [Product] ([ProductID], [Name]) VALUES (2, 'Product B')
INSERT INTO [Product] ([ProductID], [Name]) VALUES (3, 'Product C')
INSERT INTO [Project] ([ProjectID], [Name]) VALUES (1, 'Project 1')
INSERT INTO [Project] ([ProjectID], [Name]) VALUES (2, 'Project 2')
INSERT INTO [Project] ([ProjectID], [Name]) VALUES (3, 'Project 3')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (1, 'Subl a')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (2, 'Subl b')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (3, 'Subl c')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (4, 'Subl d')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (5, 'Subl e')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (6, 'Subl f')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (7, 'Subl g')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (8, 'Subl h')
INSERT INTO [ProjectProduct] ([ProjectID], [ProductID]) VALUES (1, 3)
INSERT INTO [ProjectProduct] ([ProjectID], [ProductID]) VALUES (2, 1)
INSERT INTO [ProjectProduct] ([ProjectID], [ProductID]) VALUES (2, 3)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (1,
1)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (2,
2)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (3,
3)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (4,
1)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (4,
2)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (5,
2)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (5,
3)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (6,
1)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (6,
3)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (7,
1)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (7,
2)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (7,
3)
Desired Output:
SELECT Project.Name, Project.ProjectID, SubledgerID.Name,
SubledgerID.SubledgerID FROM ?????
Producing:
Project 1, 1, Subl c, 3
Project 2, 2, Subl f, 6
Project 3, 3, Subl h, 8
Simon Withers
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!