Help with Many-to-Many-to-Many Problem

I am having a problem creating a many-to-many-to-many type relationship. It
works fine, but when I create a view to query it and test it, it does not
generate the results I expected.

Below if the DDL for the tables and the SQL for the view.

Any help would be most appreciated.

Many thanks in advance.

Regards

Keith

DIAGRAM 5: SYS_Relationship_Individuals_Courses
(http://www.step-online.org.uk/diagram5.png)

This is the relationship I am having a problem with. Each individual can
attend many courses. I have tried to model this by creating this diagram.
It has the following tables in it. SYS_Individual (to show individuals).
To show courses (which already have a many-to-many relationship
(http://www.step-online.org.uk/diagram2.png), I added all the same tables as
in diagram 2 - SYS_Courses, SYS_Courses_Venues, SYS_Courses_TimeTable,
SYS_Courses_Tutors (joined using SYS_Xref_Join_Courses). Now as each
individual could attend many courses, I assumed that the correct way to
model this would be by creating another many-to-many between the
SYS_Individual table and the SYS_Xref_Join_Courses by using a new join table
(SYS_Xref_Join_Ind_Courses).

I tried to check this works using a view (SYS_Individual_Courses_List) -
VIEW 5 below.

The problem is that no matter how many entries I put in the
SYS_Xref_Join_Ind_Courses, the test view (VIEW 5 below), only ever shows one
record. While playing around with the view, I got it to show all the
entries, but they were duplicated 4 times each! I can't remember how I did
this now either.

Now for the DDL for the tables:

CREATE TABLE [do].[SYS_Individual] (

[IND_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,

[IND_Date_Entered] [datetime] NOT NULL ,

[IND_Date_on_Project] [datetime] NULL ,

[IND_First_Name] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_Surname] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_Address] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_Post_Code] [varchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_Telephone_Home] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_Telephone_Mobile] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_Telephone_Other] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_Email_Address] [varchar] (150) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_Date_Started] [datetime] NULL ,

[IND_Trading_Name] [varchar] (150) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_Description_Proposed] [text] COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_Profile] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_DD_Economic_Activity_ID] [numeric](18, 0) NULL ,

[IND_DD_Referal_Source_Code] [numeric](18, 0) NULL ,

[IND_DD_Training_Status_ID] [numeric](18, 0) NULL ,

[IND_DD_Age_ID] [numeric](18, 0) NULL ,

[IND_DD_Potential_Business_Type_ID] [numeric](18, 0) NULL ,

[IND_DD_Exit_ID] [numeric](18, 0) NULL ,

[IND_DD_Disadvantage_ID] [numeric](18, 0) NULL ,

[IND_DD_Bank_ID] [numeric](18, 0) NULL ,

[IND_DD_Start_Up_Confirmation_ID] [numeric](18, 0) NULL ,

[IND_DD_Growth_Potential_ID] [numeric](18, 0) NULL ,

[IND_DD_Ethnicity_ID] [numeric](18, 0) NULL ,

[IND_DD_Marital_Status_ID] [numeric](18, 0) NULL ,

[IND_DD_Gender_ID] [numeric](18, 0) NULL ,

[IND_DD_Status_ID] [numeric](18, 0) NULL ,

[IND_Related_To_Another] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[IND_DD_Business_Status_ID] [numeric](18, 0) NULL ,

[IND_Last_Updated] [datetime] NULL

CREATE TABLE [dbo].[SYS_Courses] (

[COURSE_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,

[COURSE_Date_Entered] [datetime] NOT NULL ,

[COURSE_Title] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[COURSE_Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,

[COURSE_Last_Modified] [datetime] NULL

CREATE TABLE [dbo].[SYS_Courses_Venues] (

[COURSE_VEN_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,

[COURSE_VEN_Date_Entered] [datetime] NOT NULL ,

[COURSE_VEN_Address] [text] COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,

[COURSE_VEN_Last_Updated] [datetime] NULL

CREATE TABLE [dbo].[SYS_Courses_TimeTable] (

[COURSE_TT_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,

[COURSE_TT_Date_Entered] [datetime] NOT NULL ,

[COURSE_TT_Date] [datetime] NOT NULL

CREATE TABLE [dbo].[SYS_Courses_Tutors] (

[COURSE_TUT_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,

[COURSE_TUT_Date_Entered] [datetime] NOT NULL ,

[COURSE_TUT_First_Name] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[COURSE_TUT_Surname] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[COURSE_TUT_Last_Updated] [datetime] NULL

CREATE TABLE [dbo].[SYS_Xref_Join_Courses] (

[XREF_Courses_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,

[XREF_Courses_Date_Entered] [datetime] NOT NULL ,

[COURSE_ID] [numeric](18, 0) NOT NULL ,

[COURSE_VEN_ID] [numeric](18, 0) NOT NULL ,

[COURSE_TT_ID] [numeric](18, 0) NOT NULL ,

[COURSE_TUT_ID] [numeric](18, 0) NOT NULL ,

[XREF_Courses_Last_Updated] [datetime] NULL

CREATE TABLE [dbo].[SYS_Xref_Join_Ind_Courses] (

[XREF_Ind_Course_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,

[XREF_Ind_Course_Date_Entered] [datetime] NOT NULL ,

[IND_ID] [numeric](18, 0) NOT NULL ,

[XREF_Courses_ID] [numeric](18, 0) NOT NULL ,

[XREF_Ind_Course_Last_Updated] [datetime] NULL

Now for the View:

VIEW 5:

CREATE VIEW dbo.SYS_Individual_Course_List

AS

SELECT dbo.SYS_Individual.IND_First_Name,
dbo.SYS_Individual.IND_Surname, dbo.SYS_Courses.COURSE_Title,

dbo.SYS_Courses_Venues.COURSE_VEN_Address,
dbo.SYS_Xref_Join_Ind_Courses.XREF_Ind_Course_ID

FROM dbo.SYS_Courses_Venues INNER JOIN

dbo.SYS_Courses INNER JOIN

dbo.SYS_Xref_Join_Ind_Courses INNER JOIN

dbo.SYS_Individual ON
dbo.SYS_Xref_Join_Ind_Courses.IND_ID = dbo.SYS_Individual.IND_ID INNER JOIN

dbo.SYS_Xref_Join_Courses ON
dbo.SYS_Xref_Join_Ind_Courses.XREF_Ind_Course_ID =
dbo.SYS_Xref_Join_Courses.XREF_Courses_ID ON

dbo.SYS_Courses.COURSE_ID =
dbo.SYS_Xref_Join_Courses.COURSE_ID INNER JOIN

dbo.SYS_Courses_TimeTable ON
dbo.SYS_Xref_Join_Courses.COURSE_TT_ID =
dbo.SYS_Courses_TimeTable.COURSE_TT_ID INNER JOIN

dbo.SYS_Courses_Tutors ON
dbo.SYS_Xref_Join_Courses.COURSE_TUT_ID =
dbo.SYS_Courses_Tutors.COURSE_TUT_ID ON

dbo.SYS_Courses_Venues.COURSE_VEN_ID =
dbo.SYS_Xref_Join_Courses.COURSE_VEN_IDKeith et al:

> I am having a problem creating a many-to-many-to-many type relationship.
It
In a single word: `No shit'. This is by design.

> I am having a problem creating a many-to-many-to-many type relationship.
It
> works fine, but when I create a view to query it and test it, it does
not
> generate the results I expected.
You should not have expected it to work in the First Place.
If you need to create a many-to-many relationship between relations A and
B, then we create a third relation C.
Intersect relations A and B with C (that is C accepts foreign keys from
relations A and B).
Since relation C should only contain the foreign key attributes from
relations A and B you should be able to figure out how to work with it.

Do not include DDL/source-code in usenet posts. People will try to
correct your code instead of your broken logic. For best results do not
even mention your `specific implementation'.

Have fun with your new `toy',
Timothy J. Bruce
uniblab@.hotmail.com
</RANT|||"Keith" <@..> wrote in message
news:uzMhc.32757$h44.4860659@.stones.force9.net...
> I am having a problem creating a many-to-many-to-many type relationship.
It
> works fine, but when I create a view to query it and test it, it does not
> generate the results I expected.
>
> Below if the DDL for the tables and the SQL for the view.
>
> Any help would be most appreciated.
>
>
> Many thanks in advance.
>
> Regards
>
> Keith

<snip
See the reply to your previous post - as Jacco suggested, it seems that you
are using inner joins where you should be using outer joins. I suggest you
check out the examples of outer joins in Books Online, so you can see how
they work using a simple example. Your solution will probably involve left
joins from SYS_Individual to the other tables.

Simon