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
>

Related Tables: Help Needed With JOIN Query

Hi Group,

My apologies for the lengthy post, but here goes...

I have the following tables:

TABLE Vehicles
(
[ID] nvarchar(5),
[Make] nvarchar(20),
[Model] nvarchar(20),
)

TABLE [Vehicle Status]
(
[ID] int, /* this is an auto-incrementing field*/
[Vehicle ID] nvarchar(5), /* foriegn key, references Vehicles.[ID] */
[Status] nvarchar(20),
[Status Date] datetime
)

Here's my problem...

I have the following data in my [Vehicles] and [Vehicle Status] tables:

[ID] [Make] [Model]
-------
H80 Nissan Skyline
H86 Toyota Aristo

[ID] [Vehicle ID] [Status] [Status Date]
------------
1 H80 OK 2006-10-01
2 H80 Damage 2006-10-05
3 H86 OK 2006-10-13
4 H86 Dent 2006-10-15
5 H86 Scratched 2006-10-16

I need a query that will join the two tables so that the most recent
status of each vehicle can be determined. I've gotten as far as:

SELECT Vehicle.[ID], Make, Model, [Status], [Status Date] FROM
[Vehicles] INNER JOIN [Vehicle Status] ON [Vehicles].[ID] = [Vehicle
Status].[Vehicle ID]

Of course this produces the following results:

[ID] [Make] [Model] [Status] [Status Date]
--------------
H80 Nissan Skyline OK 2006-10-01
H80 Nissan Skyline Damage 2006-10-05
H86 Toyota Aristo OK 2006-10-13
H86 Toyota Aristo Dent 2006-10-15
H86 Toyota Aristo Scratched 2006-10-16

How do I filter these results so that I get only the MOST RECENT vehicle
status?

i.e:

[ID] [Make] [Model] [Status] [Status Date]
--------------
H80 Nissan Skyline Damage 2006-10-05
H86 Toyota Aristo Scratched 2006-10-16

Thanks in advance,
Rommel the iCeMAn

*** Sent via Developersdex http://www.developersdex.com ***SELECT v.[ID], Make, Model, [Status], [Status Date]
FROM
[Vehicles] v INNER JOIN [Vehicle Status] vs ON v.[ID] = vs.[Vehicle ID]
and vs.[Status Date] = (select max(vs2.[Status Date]) from [Vehicle
Status] vs2 where vs2.[Vehicle ID] = vs.[Vehicle ID])

www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***sql