Hi,
Hopefully someone can help me. I'm having difficulty with the syntax to delete a record from 4 joined tables when creating a job.
I have an 'Applicants' table linked to four other tables 'Courses', 'EmploymentHistory', 'Qualifications', and 'References' using the field 'ApplicantID'.
I want to create a job to delete all the records where the Finalised field = '0' and the record was created more than 3 days ago.
The syntax I have been using on just one of the joined tables to start with doesn't delete from the joined table:
USE OnlineApplications
DELETE Applicants
FROM Applicants
INNER JOIN Courses
ON Applicants.ApplicantID = Courses.ApplicantID
WHERE Finalised = 0 AND Created < DATEADD(d, 3, Created)
How can I delete the records from the other four tables?
ThanksHave you defined foreign key relationships?|||Darnit Poots, you beat me to it again!
I was going to ask for the joins between the tables :'(|||joins <> relationships ;)|||*shifty look*
I knew that ;)|||Have you defined foreign key relationships?
Nope, makes sense to do that I suppose. :o
For the projects I do, I merely create the tables then use the tables to store the data, haven't needed to create relationships in the past, bad development I know.
But I haven't really looked into the features of SQL Server yet.
I take it the syntax should work then if I create relationships?
Can I do this by creating a diagram?
Then I take it I need to set the joins to cascade to delete from other tables?|||Can I do this by creating a diagram?Yeah - but script it out.
Basic sample code. Check BoL for more options.
IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N't_name') BEGIN
DROP TABLE t_name
END
IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'other_t_name') BEGIN
DROP TABLE other_t_name
END
CREATE TABLE dbo.other_t_name
(
c_name INT NOT NULL CONSTRAINT df_other_t_name_c_name DEFAULT 0
, CONSTRAINT pk_other_t_name PRIMARY KEY CLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ix_other_t_name_c_name_u_nc UNIQUE NONCLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ck_other_t_name_c_name CHECK (c_name BETWEEN 1 AND 10)
)
GO
CREATE TABLE dbo.t_name
(
c_name INT NOT NULL CONSTRAINT df_t_name_c_name DEFAULT 0
, CONSTRAINT pk_t_name PRIMARY KEY CLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ix_t_name_c_name_u_nc UNIQUE NONCLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ck_t_name_c_name CHECK (c_name BETWEEN 1 AND 10)
, CONSTRAINT fk_t_name_other_t_name FOREIGN KEY (c_name) REFERENCES other_t_name (c_name) ON DELETE CASCADE
)
GO
HTH
Showing posts with label creating. Show all posts
Showing posts with label creating. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
Help with Select statement
I need help creating a select statement that will show users who have
attempted to fill out a form more than once and have not completed it
at least once.
Here is the basic table layout.
ID int PK
PersonID int
Completed bit
Date DateTime
The data looks like this
ID Person ID Completed Date
1 101 True 5/1/06
2 102 True 5/2/06
3 103 False 5/3/06
4 104 True 5/3/06
5 103 True 5/3/06
6 105 False 5/3/06
7 105 True 5/4/06
8 105 False 5/4/06
9 106 True 5/4/06
10 104 True 5/4/06
I need to select all users who have attempted to fill out the form
multiple times and have failed to complete the form(a False in the
Completed column) at least one of those times. So for the above data
I would want the results of the select to be
ID Person ID Completed Date
3 103 False 5/3/06
5 103 True 5/3/06
6 105 False 5/3/06
7 105 True 5/4/06
8 105 False 5/4/06
Can anyone help me with this.
Thanksselect ID,
PersonID,
Completed,
Date
from mytable
where PersonID in (
select PersonID
from mytable
group by PersonID
having count(*)>1
and min(cast(Completed as int))=0)|||It worked great. Thank You.
attempted to fill out a form more than once and have not completed it
at least once.
Here is the basic table layout.
ID int PK
PersonID int
Completed bit
Date DateTime
The data looks like this
ID Person ID Completed Date
1 101 True 5/1/06
2 102 True 5/2/06
3 103 False 5/3/06
4 104 True 5/3/06
5 103 True 5/3/06
6 105 False 5/3/06
7 105 True 5/4/06
8 105 False 5/4/06
9 106 True 5/4/06
10 104 True 5/4/06
I need to select all users who have attempted to fill out the form
multiple times and have failed to complete the form(a False in the
Completed column) at least one of those times. So for the above data
I would want the results of the select to be
ID Person ID Completed Date
3 103 False 5/3/06
5 103 True 5/3/06
6 105 False 5/3/06
7 105 True 5/4/06
8 105 False 5/4/06
Can anyone help me with this.
Thanksselect ID,
PersonID,
Completed,
Date
from mytable
where PersonID in (
select PersonID
from mytable
group by PersonID
having count(*)>1
and min(cast(Completed as int))=0)|||It worked great. Thank You.
Wednesday, March 21, 2012
Help with reuse methology?
I find that many stored procedures are necessary in nearly all databases I
use. Creating dynamic (one that takes a string and executes the sql in it)
stored procedures is expensive performance wise. However, they do allow you
to pass in strings of sql and execute it. I find in general that SPs are
created and can only be used for one specific thing - registration form
input, account info update, etc. For example, I can't take the account
update SP and use it for the registration input SP.
Is there some general or fundamental guide that should be followed to lower
the overall number of stored procedures in a database?
Thanks,
BrettBrett wrote:
> I find that many stored procedures are necessary in nearly all
> databases I use. Creating dynamic (one that takes a string and
> executes the sql in it) stored procedures is expensive performance
> wise. However, they do allow you to pass in strings of sql and
> execute it. I find in general that SPs are created and can only be
> used for one specific thing - registration form input, account info
> update, etc. For example, I can't take the account update SP and use
> it for the registration input SP.
> Is there some general or fundamental guide that should be followed to
> lower the overall number of stored procedures in a database?
> Thanks,
> Brett
Not really. You need what you need. Dynamic SQL is not the way to go.
It's really no different than executing the SQL statement directly from
the client and exposes your database to SQL injection and performance
issues.
Writing stored procedures is just a part of the application development
process. You can leverage things like functions or utility-type stored
procs to simplify repetitive code, but not much you can do to lower the
number of procedures you need.
David Gugick
Imceda Software
www.imceda.com
use. Creating dynamic (one that takes a string and executes the sql in it)
stored procedures is expensive performance wise. However, they do allow you
to pass in strings of sql and execute it. I find in general that SPs are
created and can only be used for one specific thing - registration form
input, account info update, etc. For example, I can't take the account
update SP and use it for the registration input SP.
Is there some general or fundamental guide that should be followed to lower
the overall number of stored procedures in a database?
Thanks,
BrettBrett wrote:
> I find that many stored procedures are necessary in nearly all
> databases I use. Creating dynamic (one that takes a string and
> executes the sql in it) stored procedures is expensive performance
> wise. However, they do allow you to pass in strings of sql and
> execute it. I find in general that SPs are created and can only be
> used for one specific thing - registration form input, account info
> update, etc. For example, I can't take the account update SP and use
> it for the registration input SP.
> Is there some general or fundamental guide that should be followed to
> lower the overall number of stored procedures in a database?
> Thanks,
> Brett
Not really. You need what you need. Dynamic SQL is not the way to go.
It's really no different than executing the SQL statement directly from
the client and exposes your database to SQL injection and performance
issues.
Writing stored procedures is just a part of the application development
process. You can leverage things like functions or utility-type stored
procs to simplify repetitive code, but not much you can do to lower the
number of procedures you need.
David Gugick
Imceda Software
www.imceda.com
Monday, February 27, 2012
Help with nearest neighbour problem
Hi,
I need help with creating an sql statement that determines the nearest neighbour in a lookup table to my dataset by its date column. My attempts minimizing the date difference in a cross join are lacking performance.
I have two tables:
Table 1 (Data; 13000 datasets):
ID, date
==========
1, 12.12.2006
2, 28.12.2006
3, 05.01.2007
and Table 2 (Lookup; 4000 datasets):
date, margin
==========
05.12.2006, 2.80
27.12.2006, 2.86
01.01.2007, 3.01
10.01.2007, 2.99
Expected result:
ID, date, margin
==========
1, 12.12.2006, 2.80
2, 28.12.2006, 2.86
3, 05.01.2007, 3.01
Any help is much desired
Kaicould you explain please how to calculate "nearest"|||could you explain please how to calculate "nearest"
For a given date in the data table I'm looking for the closest date in the lookup table, e.g. MIN(Datediff(dd,date(data),date(lookup))).|||that datediff might produce negative numbers, and MIN will take the largest negative number
do you perhaps mean MIN(ABS(...)) ?|||that datediff might produce negative numbers, and MIN will take the largest negative number
do you perhaps mean MIN(ABS(...)) ?
You're right, it's MIN(ABS(...)). But how do I integrate this into a view to do effective lookups?|||effective? i would imagine this to depend on the existence of appropriate indexes
the following works (i tested it on your data) but i dunno how slow it's gonna be for your large tables...with X
( ID
, TDate
, a
, LDate
, margin
)
as (
select T.ID
, T.Date as TDate
, abs(datediff(dd,T.Date,L.Date)) as a
, L.Date as LDate
, L.margin
from table1 as T
cross
join lookup as L
)
select ID
, TDate
, a
, LDate
, margin
from X as D1
where a =
( select min(a)
from X
where ID = D1.ID )|||That's quite some nifty code. Unfortunately I had to rewrite the code to not use the "WITH" statement as MS SQL Server 2000 apparently doesn't support this. Anyway I've came down to 1 min. processing time from 57 min. without touching the indexes, so thanks a lot.
This is my final code:
Select ID
, TDate
, a
, LDate
, margin
from
(
select T.ID
, T.Date as TDate
, abs(datediff(dd,T.Date,L.Date)) as a
, L.Date as LDate
, L.margin
from table1 as T
cross
join lookup as L
) X
where a =
(
select min(abs(datediff(dd,T.Date,L.Date))) as amin
from table1 as T
cross
join lookup as L
where T.ID = X.ID
)
I need help with creating an sql statement that determines the nearest neighbour in a lookup table to my dataset by its date column. My attempts minimizing the date difference in a cross join are lacking performance.
I have two tables:
Table 1 (Data; 13000 datasets):
ID, date
==========
1, 12.12.2006
2, 28.12.2006
3, 05.01.2007
and Table 2 (Lookup; 4000 datasets):
date, margin
==========
05.12.2006, 2.80
27.12.2006, 2.86
01.01.2007, 3.01
10.01.2007, 2.99
Expected result:
ID, date, margin
==========
1, 12.12.2006, 2.80
2, 28.12.2006, 2.86
3, 05.01.2007, 3.01
Any help is much desired
Kaicould you explain please how to calculate "nearest"|||could you explain please how to calculate "nearest"
For a given date in the data table I'm looking for the closest date in the lookup table, e.g. MIN(Datediff(dd,date(data),date(lookup))).|||that datediff might produce negative numbers, and MIN will take the largest negative number
do you perhaps mean MIN(ABS(...)) ?|||that datediff might produce negative numbers, and MIN will take the largest negative number
do you perhaps mean MIN(ABS(...)) ?
You're right, it's MIN(ABS(...)). But how do I integrate this into a view to do effective lookups?|||effective? i would imagine this to depend on the existence of appropriate indexes
the following works (i tested it on your data) but i dunno how slow it's gonna be for your large tables...with X
( ID
, TDate
, a
, LDate
, margin
)
as (
select T.ID
, T.Date as TDate
, abs(datediff(dd,T.Date,L.Date)) as a
, L.Date as LDate
, L.margin
from table1 as T
cross
join lookup as L
)
select ID
, TDate
, a
, LDate
, margin
from X as D1
where a =
( select min(a)
from X
where ID = D1.ID )|||That's quite some nifty code. Unfortunately I had to rewrite the code to not use the "WITH" statement as MS SQL Server 2000 apparently doesn't support this. Anyway I've came down to 1 min. processing time from 57 min. without touching the indexes, so thanks a lot.
This is my final code:
Select ID
, TDate
, a
, LDate
, margin
from
(
select T.ID
, T.Date as TDate
, abs(datediff(dd,T.Date,L.Date)) as a
, L.Date as LDate
, L.margin
from table1 as T
cross
join lookup as L
) X
where a =
(
select min(abs(datediff(dd,T.Date,L.Date))) as amin
from table1 as T
cross
join lookup as L
where T.ID = X.ID
)
Friday, February 24, 2012
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_ID
Keith 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
|||>> I am having a problem creating a many-to-many-to-many type
relationship. It
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@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.c om...
> relationship. It
> 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.
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:
[]
> 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>
>
That's rather poor advice. What posters should do is present as simple a
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:
[]
> 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
>
Don't use SSN, assign a unique student ID.
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@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.c om...
> relationship. It
> works fine, .. <<
>
You cannot create a true three-way
> relationship as a series of binary relationships; look up join-project
> normal forms.
>
Yes, you can- sometimes (often?). See
http://www.cis.drexel.edu/faculty/song/Papers/Jdb99.pdf
|||>> 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. <<
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@.magicinterface.com> wrote:
[vbcol=seagreen]
>Timothy J. Bruce wrote:
>[]
>That's rather poor advice. What posters should do is present as simple a
>sample of the problem as possible, including standard SQL. Mentioning the
>specific tools and environment used would be helpful.
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.
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:
> 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
|||>> I am having a problem creating a many-to-many-to-many type
relationship. It
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@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.c om...
> relationship. It
> 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.
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:
[]
> 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>
>
That's rather poor advice. What posters should do is present as simple a
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:
[]
> 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
>
Don't use SSN, assign a unique student ID.
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@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.c om...
> relationship. It
> works fine, .. <<
>
You cannot create a true three-way
> relationship as a series of binary relationships; look up join-project
> normal forms.
>
Yes, you can- sometimes (often?). See
http://www.cis.drexel.edu/faculty/song/Papers/Jdb99.pdf
|||>> 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. <<
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@.magicinterface.com> wrote:
[vbcol=seagreen]
>Timothy J. Bruce wrote:
>[]
>That's rather poor advice. What posters should do is present as simple a
>sample of the problem as possible, including standard SQL. Mentioning the
>specific tools and environment used would be helpful.
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.
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.
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>> I am having a problem creating a many-to-many-to-many type
relationship. It
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@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.c om...
> >> I am having a problem creating a many-to-many-to-many type
> relationship. It
> 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.
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|||Keith 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|||>> 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. <<
Years ago, the policy in many US universities was to make all students
get a Social Security Number (ssn); you needed one to work anyway.
Later, schools kept a block of bogus SSNs. The Social Security Number
(SSN) is composed of 3 parts, XXX-XX-XXXX, called the Area, Group, and
Serial. For the most part, (there are exceptions), the Area is
determined by where the individual APPLIED for the SSN (before 1972) or
RESIDED at time of application (after 1972). The "bogus" areas are
assigned as follows:
000 unused
627-699 unassigned, for future use
700-728 Railroad workers through 1963, then discontinued. Anyone in
the Railroad retirement program is now dead, over 120 years of age or
converted to an SSN.
729-899 unassigned, for future use.
900-999 not valid SSNs, but were used for program purposes when state
aid to the aged, blind and disabled was converted to a federal program
administered by SSA. Again, this goes back to the 1930's and as far as I
know anyone in those programs is now dead or converted to an SSN.
The 627-699 areas let you treat each foreign student group separately
with its own area number.
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>Years ago, the policy in many US universities was to make all students
>get a Social Security Number (ssn); you needed one to work anyway.
>Later, schools kept a block of bogus SSNs. The Social Security Number
>(SSN) is composed of 3 parts, XXX-XX-XXXX, called the Area, Group, and
>Serial. For the most part, (there are exceptions), the Area is
>determined by where the individual APPLIED for the SSN (before 1972) or
>RESIDED at time of application (after 1972). The "bogus" areas are
>assigned as follows:
> 000 unused
> 627-699 unassigned, for future use
> 700-728 Railroad workers through 1963, then discontinued. Anyone in
>the Railroad retirement program is now dead, over 120 years of age or
>converted to an SSN.
> 729-899 unassigned, for future use.
> 900-999 not valid SSNs, but were used for program purposes when state
>aid to the aged, blind and disabled was converted to a federal program
>administered by SSA. Again, this goes back to the 1930's and as far as I
>know anyone in those programs is now dead or converted to an SSN.
>The 627-699 areas let you treat each foreign student group separately
>with its own area number.
>--CELKO--
Very enlightning. Thanks.
Got any more info or links where I can find out more info on this.
Randy
http://members.aol.com/rsmeiner|||>> Got any more info or links where I can find out more info on this. <<
Try doing a Google and go to the SSA site.
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>>> Got any more info or links where I can find out more info on this. <<
>Try doing a Google and go to the SSA site.
>--CELKO--
You mean you actually expect me to do my own research ?
Well, ok.
Randy
http://members.aol.com/rsmeiner|||"RSMEINER" <rsmeiner@.aol.comcrap> wrote in message
news:20040425123019.27697.00000064@.mb-m17.aol.com...
> >Years ago, the policy in many US universities was to make all students
> >get a Social Security Number (ssn); you needed one to work anyway.
Years ago. This is no longer true, as places like NYS outlawed that use.
RPI had to convert to a new internal ID.
So, SSN may not always be a great choice.
However, as Joe's sure to ask or point out, the RPI assigned ID exists
independent of any single database.
i.e. they're not using some random IDENTITY field and going from there.|||Timothy J. Bruce wrote:
[]
> 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>
>
That's rather poor advice. What posters should do is present as simple a
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:
[]
> 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
>
Don't use SSN, assign a unique student ID.
--
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@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.c om...
> >> I am having a problem creating a many-to-many-to-many type
> relationship. It
> works fine, .. <<
You cannot create a true three-way
> relationship as a series of binary relationships; look up join-project
> normal forms.
Yes, you can- sometimes (often?). See
http://www.cis.drexel.edu/faculty/song/Papers/Jdb99.pdf|||>> I am having a problem creating a many-to-many-to-many type
relationship. It
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@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.c om...
> >> I am having a problem creating a many-to-many-to-many type
> relationship. It
> 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.
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|||>> 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. <<
Years ago, the policy in many US universities was to make all students
get a Social Security Number (ssn); you needed one to work anyway.
Later, schools kept a block of bogus SSNs. The Social Security Number
(SSN) is composed of 3 parts, XXX-XX-XXXX, called the Area, Group, and
Serial. For the most part, (there are exceptions), the Area is
determined by where the individual APPLIED for the SSN (before 1972) or
RESIDED at time of application (after 1972). The "bogus" areas are
assigned as follows:
000 unused
627-699 unassigned, for future use
700-728 Railroad workers through 1963, then discontinued. Anyone in
the Railroad retirement program is now dead, over 120 years of age or
converted to an SSN.
729-899 unassigned, for future use.
900-999 not valid SSNs, but were used for program purposes when state
aid to the aged, blind and disabled was converted to a federal program
administered by SSA. Again, this goes back to the 1930's and as far as I
know anyone in those programs is now dead or converted to an SSN.
The 627-699 areas let you treat each foreign student group separately
with its own area number.
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>Years ago, the policy in many US universities was to make all students
>get a Social Security Number (ssn); you needed one to work anyway.
>Later, schools kept a block of bogus SSNs. The Social Security Number
>(SSN) is composed of 3 parts, XXX-XX-XXXX, called the Area, Group, and
>Serial. For the most part, (there are exceptions), the Area is
>determined by where the individual APPLIED for the SSN (before 1972) or
>RESIDED at time of application (after 1972). The "bogus" areas are
>assigned as follows:
> 000 unused
> 627-699 unassigned, for future use
> 700-728 Railroad workers through 1963, then discontinued. Anyone in
>the Railroad retirement program is now dead, over 120 years of age or
>converted to an SSN.
> 729-899 unassigned, for future use.
> 900-999 not valid SSNs, but were used for program purposes when state
>aid to the aged, blind and disabled was converted to a federal program
>administered by SSA. Again, this goes back to the 1930's and as far as I
>know anyone in those programs is now dead or converted to an SSN.
>The 627-699 areas let you treat each foreign student group separately
>with its own area number.
>--CELKO--
Very enlightning. Thanks.
Got any more info or links where I can find out more info on this.
Randy
http://members.aol.com/rsmeiner|||>> Got any more info or links where I can find out more info on this. <<
Try doing a Google and go to the SSA site.
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>>> Got any more info or links where I can find out more info on this. <<
>Try doing a Google and go to the SSA site.
>--CELKO--
You mean you actually expect me to do my own research ?
Well, ok.
Randy
http://members.aol.com/rsmeiner|||"RSMEINER" <rsmeiner@.aol.comcrap> wrote in message
news:20040425123019.27697.00000064@.mb-m17.aol.com...
> >Years ago, the policy in many US universities was to make all students
> >get a Social Security Number (ssn); you needed one to work anyway.
Years ago. This is no longer true, as places like NYS outlawed that use.
RPI had to convert to a new internal ID.
So, SSN may not always be a great choice.
However, as Joe's sure to ask or point out, the RPI assigned ID exists
independent of any single database.
i.e. they're not using some random IDENTITY field and going from there.|||Timothy J. Bruce wrote:
[]
> 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>
>
That's rather poor advice. What posters should do is present as simple a
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:
[]
> 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
>
Don't use SSN, assign a unique student ID.
--
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@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.c om...
> >> I am having a problem creating a many-to-many-to-many type
> relationship. It
> works fine, .. <<
You cannot create a true three-way
> relationship as a series of binary relationships; look up join-project
> normal forms.
Yes, you can- sometimes (often?). See
http://www.cis.drexel.edu/faculty/song/Papers/Jdb99.pdf|||>> 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. <<
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.|||>> 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. <<
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@.magicinterface.com> wrote:
>Timothy J. Bruce wrote:
>[]
>>
>> 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
>That's rather poor advice. What posters should do is present as simple a
>sample of the problem as possible, including standard SQL. Mentioning the
>specific tools and environment used would be helpful.
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.|||joe.celko@.northface.edu (--CELKO--) 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. <<
>1) New York State used to make them get an SSN.
What do you do in the meantime for a PK? I--a Canadian--apply for
admission, and you will not admit me because I do not have an SSN? I
think I would want to take database courses elsewhere then!
>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
And if the definition changes?
>3) invent a number if your state has privacy laws that require the SSN
>not be used.
I think #3 is best. 1) If your jurisdictions do not have privacy
laws, it does not mean that they never will. Some came into effect in
Canada this last New Year's Day. 2) If it is Their number, what do
you do if They change it or its definition?
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.|||Ed Prochak <ed.prochak@.magicinterface.com> wrote:
>Timothy J. Bruce wrote:
>[]
>>
>> 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
>That's rather poor advice. What posters should do is present as simple a
>sample of the problem as possible, including standard SQL. Mentioning the
>specific tools and environment used would be helpful.
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.|||joe.celko@.northface.edu (--CELKO--) 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. <<
>1) New York State used to make them get an SSN.
What do you do in the meantime for a PK? I--a Canadian--apply for
admission, and you will not admit me because I do not have an SSN? I
think I would want to take database courses elsewhere then!
>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
And if the definition changes?
>3) invent a number if your state has privacy laws that require the SSN
>not be used.
I think #3 is best. 1) If your jurisdictions do not have privacy
laws, it does not mean that they never will. Some came into effect in
Canada this last New Year's Day. 2) If it is Their number, what do
you do if They change it or its definition?
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
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.
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
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|||>> I am having a problem creating a many-to-many-to-many type
relationship. It
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@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.com...
> relationship. It
> 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.
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:
[]
> 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>
>
That's rather poor advice. What posters should do is present as simple a
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 i
n
the specific area and general questions in the general area. IOW, they shoul
d
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 threa
d
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 th
e
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:
[]
> Just from curiosity - and I don't claim to have any answer to this questio
n
> 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 wher
e
> they were born.
> Simon
>
Don't use SSN, assign a unique student ID.
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@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.com...
> relationship. It
> works fine, .. <<
>
You cannot create a true three-way
> relationship as a series of binary relationships; look up join-project
> normal forms.
>
Yes, you can- sometimes (often?). See
http://www.cis.drexel.edu/faculty/song/Papers/Jdb99.pdf|||>> 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. <<
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@.magicinterface.com> wrote:
>Timothy J. Bruce wrote:
>[]
[vbcol=seagreen]
>That's rather poor advice. What posters should do is present as simple a
>sample of the problem as possible, including standard SQL. Mentioning the
>specific tools and environment used would be helpful.
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.
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|||>> I am having a problem creating a many-to-many-to-many type
relationship. It
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@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.com...
> relationship. It
> 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.
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:
[]
> 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>
>
That's rather poor advice. What posters should do is present as simple a
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 i
n
the specific area and general questions in the general area. IOW, they shoul
d
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 threa
d
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 th
e
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:
[]
> Just from curiosity - and I don't claim to have any answer to this questio
n
> 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 wher
e
> they were born.
> Simon
>
Don't use SSN, assign a unique student ID.
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@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.com...
> relationship. It
> works fine, .. <<
>
You cannot create a true three-way
> relationship as a series of binary relationships; look up join-project
> normal forms.
>
Yes, you can- sometimes (often?). See
http://www.cis.drexel.edu/faculty/song/Papers/Jdb99.pdf|||>> 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. <<
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@.magicinterface.com> wrote:
>Timothy J. Bruce wrote:
>[]
[vbcol=seagreen]
>That's rather poor advice. What posters should do is present as simple a
>sample of the problem as possible, including standard SQL. Mentioning the
>specific tools and environment used would be helpful.
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.
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|||>> I am having a problem creating a many-to-many-to-many type
relationship. It
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@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.com...
> >> I am having a problem creating a many-to-many-to-many type
> relationship. It
> 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.
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:
[]
> 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>
>
That's rather poor advice. What posters should do is present as simple a
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:
[]
> 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
>
Don't use SSN, assign a unique student ID.
--
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@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.com...
> >> I am having a problem creating a many-to-many-to-many type
> relationship. It
> works fine, .. <<
>
You cannot create a true three-way
> relationship as a series of binary relationships; look up join-project
> normal forms.
>
Yes, you can- sometimes (often?). See
http://www.cis.drexel.edu/faculty/song/Papers/Jdb99.pdf|||>> 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. <<
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@.magicinterface.com> wrote:
>Timothy J. Bruce wrote:
>[]
>> 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>
>That's rather poor advice. What posters should do is present as simple a
>sample of the problem as possible, including standard SQL. Mentioning the
>specific tools and environment used would be helpful.
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.|||joe.celko@.northface.edu (--CELKO--) 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. <<
>1) New York State used to make them get an SSN.
What do you do in the meantime for a PK? I--a Canadian--apply for
admission, and you will not admit me because I do not have an SSN? I
think I would want to take database courses elsewhere then!
>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
And if the definition changes?
>3) invent a number if your state has privacy laws that require the SSN
>not be used.
I think #3 is best. 1) If your jurisdictions do not have privacy
laws, it does not mean that they never will. Some came into effect in
Canada this last New Year's Day. 2) If it is Their number, what do
you do if They change it or its definition?
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
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|||>> I am having a problem creating a many-to-many-to-many type
relationship. It
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@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.com...
> >> I am having a problem creating a many-to-many-to-many type
> relationship. It
> 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.
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:
[]
> 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>
>
That's rather poor advice. What posters should do is present as simple a
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:
[]
> 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
>
Don't use SSN, assign a unique student ID.
--
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@.northface.edu> wrote in message
news:a264e7ea.0404231740.7f98d9d5@.posting.google.com...
> >> I am having a problem creating a many-to-many-to-many type
> relationship. It
> works fine, .. <<
>
You cannot create a true three-way
> relationship as a series of binary relationships; look up join-project
> normal forms.
>
Yes, you can- sometimes (often?). See
http://www.cis.drexel.edu/faculty/song/Papers/Jdb99.pdf|||>> 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. <<
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@.magicinterface.com> wrote:
>Timothy J. Bruce wrote:
>[]
>> 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>
>That's rather poor advice. What posters should do is present as simple a
>sample of the problem as possible, including standard SQL. Mentioning the
>specific tools and environment used would be helpful.
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.|||joe.celko@.northface.edu (--CELKO--) 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. <<
>1) New York State used to make them get an SSN.
What do you do in the meantime for a PK? I--a Canadian--apply for
admission, and you will not admit me because I do not have an SSN? I
think I would want to take database courses elsewhere then!
>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
And if the definition changes?
>3) invent a number if your state has privacy laws that require the SSN
>not be used.
I think #3 is best. 1) If your jurisdictions do not have privacy
laws, it does not mean that they never will. Some came into effect in
Canada this last New Year's Day. 2) If it is Their number, what do
you do if They change it or its definition?
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
Subscribe to:
Posts (Atom)