Wednesday, March 28, 2012

Relationship Problem

Hi
Fairly new to SQL so apologies if this is really dumb.
Can anyone see what is wrong with the relationship here
(http://www.step-online.org.uk/Diagram.png)
I two many-to-many relationships which I have tried to make a
many-to-many-to-many relationship out of!
I have Individuals and Courses.
Courses can be held at Many Locations by Many Tutors - many-to-many
relationship 1 - this works fine.
Many Individuals can attend Many Courses - so I have created another join
table and tried joining it to the courses table (which is part of the
previous many-to-many join).
It works, but I get each result multiplied x 4. So if I enter that one
individual attended 1 course and run it, it gives me the result 4 times!
Making it look like he attended the same course 4 times!
Have I done something wrong here?Forgot to say - there is a 3rd many-to-many relationship involved!
An Individual can be linked to many Businesses also!
"Keith" <@..> wrote in message news:ewdyEuwFEHA.3540@.TK2MSFTNGP09.phx.gbl...
> Hi
> Fairly new to SQL so apologies if this is really dumb.
> Can anyone see what is wrong with the relationship here
> (http://www.step-online.org.uk/Diagram.png)
> I two many-to-many relationships which I have tried to make a
> many-to-many-to-many relationship out of!
> I have Individuals and Courses.
> Courses can be held at Many Locations by Many Tutors - many-to-many
> relationship 1 - this works fine.
> Many Individuals can attend Many Courses - so I have created another join
> table and tried joining it to the courses table (which is part of the
> previous many-to-many join).
> It works, but I get each result multiplied x 4. So if I enter that one
> individual attended 1 course and run it, it gives me the result 4 times!
> Making it look like he attended the same course 4 times!
> Have I done something wrong here?
>|||If a single instance of a course given at a particular time can have
multiple tutors ( say 4), I can see where you might get 4 rows.. It would be
better for you to post your query also..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Keith" <@..> wrote in message news:ewdyEuwFEHA.3540@.TK2MSFTNGP09.phx.gbl...
> Hi
> Fairly new to SQL so apologies if this is really dumb.
> Can anyone see what is wrong with the relationship here
> (http://www.step-online.org.uk/Diagram.png)
> I two many-to-many relationships which I have tried to make a
> many-to-many-to-many relationship out of!
> I have Individuals and Courses.
> Courses can be held at Many Locations by Many Tutors - many-to-many
> relationship 1 - this works fine.
> Many Individuals can attend Many Courses - so I have created another join
> table and tried joining it to the courses table (which is part of the
> previous many-to-many join).
> It works, but I get each result multiplied x 4. So if I enter that one
> individual attended 1 course and run it, it gives me the result 4 times!
> Making it look like he attended the same course 4 times!
> Have I done something wrong here?
>|||My tables are just full of dummy info at the moment.
2 records in every table with the exception of individual which has 3
records.
There are 3 individual-business joins, 6 course joins, and 1
individual-course join (the one that gives me 4 copies).
Simple SQL statement below (this will not be the final statement - just
testing that it worked).
Thanks
SELECT dbo.SYS_Individual.IND_First_Name,
dbo.SYS_Individual.IND_Surname, dbo.SYS_Courses.COURSE_Title
FROM dbo.SYS_Courses INNER JOIN
dbo.SYS_Xref_Join_Courses 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 INNER JOIN
dbo.SYS_Courses_Venues ON
dbo.SYS_Xref_Join_Courses.COURSE_VEN_ID =
dbo.SYS_Courses_Venues.COURSE_VEN_ID INNER JOIN
dbo.SYS_Xref_Join_Ind_Courses ON
dbo.SYS_Courses.COURSE_ID = dbo.SYS_Xref_Join_Ind_Courses.COURSE_ID INNER
JOIN
dbo.SYS_Individual ON
dbo.SYS_Xref_Join_Ind_Courses.IND_ID = dbo.SYS_Individual.IND_ID
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:O6wMiNxFEHA.576@.TK2MSFTNGP11.phx.gbl...
> If a single instance of a course given at a particular time can have
> multiple tutors ( say 4), I can see where you might get 4 rows.. It would
be
> better for you to post your query also..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Keith" <@..> wrote in message
news:ewdyEuwFEHA.3540@.TK2MSFTNGP09.phx.gbl...
join
>

No comments:

Post a Comment