One to Many relationship

Dev Shed Forums Sponsor:

October 9th, 2003, 02:45 AM

connollyg

Registered User

Join Date: Oct 2003

Location: England

Posts: 5

Time spent in forums: 5 m 3 sec
Reputation Power: 0

One to Many relationship

I have two tables

CREW
CrewID
Person1
Person2

PARTAKER
PartakerID
LastName

Fields "Person1" and "Person2" both point to Table "PARTAKER"

i am having real difficulty getting an SQL statement to retrieve both names on the same row, has anyone got any ideas on what the correct syntax is?

This statement gives me two rows:-

SELECT Crew.CrewID,Partaker.LastName
FROM Crew, Partaker
WHERE
(Crew.Person1=Partaker.PartakerID)
OR
(Crew.Person2=partaker.partakerid)

G

October 9th, 2003, 02:52 AM

tank80

php-oriented object

Join Date: Jul 2001

Location: 0x9832053

Posts: 173

Time spent in forums: 24 m 49 sec
Reputation Power: 8

i suppose you have data in a similar way:

Code:

CrewID   |   Person1   |   Person2
-----------------------------------------
    1            Frank
    2                           Mike

since the two records are different, it's right to get them in two different rows.
It should be wrong if you have data like this:

Code:

CrewID   |   Person1   |   Person2
-----------------------------------------
    1            Frank      Mike

and if you get two rows, you have to modify your query by adding a DISTINCT before SELECT.

hope it's clear enough

October 9th, 2003, 03:32 AM

connollyg

Registered User

Join Date: Oct 2003

Location: England

Posts: 5

Time spent in forums: 5 m 3 sec
Reputation Power: 0

No its the second one!

I have two person crews, and i want to somehow relate the two members together, i have other info in the CREW table that is unique to the crew and not to the individuals!

G

October 9th, 2003, 07:05 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,919

Time spent in forums: 1 Month 4 Weeks 12 h 51 m 6 sec
Reputation Power: 1018

Code:

select Crew.CrewID
     , crew1.LastName 
     , crew2.lastname
  from Crew
left outer
  join Partaker crew1
    on Crew.Person1 = crew1.PartakerID
left outer
  join Partaker crew2
    on Crew.Person2 = crew2.PartakerID

rudy
http://r937.com/

October 10th, 2003, 11:35 AM

connollyg

Registered User

Join Date: Oct 2003

Location: England

Posts: 5

Time spent in forums: 5 m 3 sec
Reputation Power: 0

Rudy,

That gives me a syntax error in MS Access

I also dont understand how it works can you explain please?

G

October 10th, 2003, 12:46 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,919

Time spent in forums: 1 Month 4 Weeks 12 h 51 m 6 sec
Reputation Power: 1018

Code:

select Crew.CrewID
     , crew1.LastName 
     , crew2.lastname
  from (
       Crew
left outer
  join Partaker crew1
    on Crew.Person1 = crew1.PartakerID
       )
left outer
  join Partaker crew2
    on Crew.Person2 = crew2.PartakerID

silly access, needs to have its joins parenthesized if there are more than two tables involved

the way it works is that it joins the Crew table to two "copies" of the Partaker table

rudy

October 12th, 2003, 07:55 AM

connollyg

Registered User

Join Date: Oct 2003

Location: England

Posts: 5

Time spent in forums: 5 m 3 sec
Reputation Power: 0

Rudy,

Thanks, that worked a treat, i have been trying to get my head round that for about a week, and in the end it was so simple. I have bought myself a book on SQL now so hopefully i wont have any more simple questions like that one!

G

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: