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_ID

Keith et al:

In a single word: `No shit'. This is by design.

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 (AT) hotmail (DOT) com
</RANT>

"Keith" <@.> wrote

<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

works fine, .. <<

No, you seem to have 5NF problems. You cannot create a true three-way
relationship as a series of binary relationships; look up join-project
normal forms.

But you have a lot of other problems.

1) Why did you make everything NUMERIC(18,0)? Think about what an
amazing thing that would be if reality was like that.

2) Why did you use IDENTITY instead of looking for real keys??

3) Why didn't you follow ISO-11179 naming rules? Terrible prefixes,
lack of any industry standards for columns, etc.

4) Why put physical history into the tables? There are tools for
that.

5) Isn't a venue an attribute of a course?

6) What is the logical meaning of those XREF tables in terms of a
logical data model?

7) What is a "_type_id"?? An attribute is either a type or it is an
identifier, but never both. Again, you don't understand the
differences between data and metadata, so you mix them in wreird ways.

Your DDL ought to look more like this:

CREATE TABLE IndividualCourses
(ssn CHAR(9) NOT NULL
REFERENCES Individuals (ssn)
ON UPDATE CASCADE
ON DELETE CASCADE,
course_id CHAR(5) NOT NULL)
REFERENCES Courses(course_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (ssn, course_id));

You need constraints, defaults, real keys, logical names, etc. Start
over and get a book on data modeling.

"--CELKO--" <joe.celko (AT) northface (DOT) edu> wrote

Just from curiosity - and I don't claim to have any answer to this question
myself - what primary key would you use for European students? The SSN
doesn't exist, and students routinely study in a country which is not where
they were born.

Simon

Timothy J. Bruce wrote:

[]
sample of the problem as possible, including standard SQL. Mentioning the
specific tools and environment used would be helpful.

The related suggestion is that posters should post DBMS specific questions in
the specific area and general questions in the general area. IOW, they should
post on topic questions.

How many ORACLE or SQL SERVER specific questions have we seen here in the
generic comp.databases group? Too many. So the original poster on this thread
was misguided in his posting to both specific SQL SERVER groups and to the
comp.databases group. Such a posting is nearly always off topic in one or the
other group.

Since his question was about relations and not SQL SERVER syntax, the
generic databases group is more appropriate.

If replies are just picking apart syntax, then I would suggest they are off
topic in the comp.databases group (unless maybe they are pointing out
something is not Standard SQL).

Bottom line is:
1. post to the appropriate group (goes for both original and reply posting)
2. post as complete and succuint information as possible (including your
platform DB and OS) for the comp.databases group.


--
Ed Prochak
running http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost

Simon Hayes wrote:

[]

--
Ed Prochak
running http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost

"--CELKO--" <joe.celko (AT) northface (DOT) edu> wrote


Yes, you can- sometimes (often?). See
http://www.cis.drexel.edu/faculty/song/Papers/Jdb99.pdf

myself - what primary key would you use for European students? The SSN
doesn't exist, and students routinely study in a country which is not
where they were born. <<

1) New York State used to make them get an SSN.

2) Use the holes in the Social Security Number. The SSN is composed of
3 parts, XXX-XX-XXXX, called the Area, Group, and Serial. The areas
are assigned as follows:

000 unused
627-699 unassigned, for future use
729-899 unassigned, for future use
900-999 not valid SSNs

3) invent a number if your state has privacy laws that require the SSN
not be used.

Ed Prochak <ed.prochak (AT) magicinterface (DOT) com> wrote:

I will second this most strenuously. Details help. For example,
sometimes, a problem is due to a bug with a particular version of a
particular DBMS. If a poster is going to make me guess about details
like this, I guess I will probably just move on to the next post.

[snip]

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.