Joining multiple tables in a view.

I have three tables
1st table is Student
StudnetID (pk)
Other fields…
2nd table is PhoneType
PhoneTypeID (pk)
PhoneType
3rd table is StudentHasPhone
SHPID (pk)
StudnetID (fk)
PhoneTypeID (fk)
PhoneNumber
PhoneType is an auxiliary table that has 5 records in it Home phone, Cell
phone, Work phone, Pager, and Fax. Is there a way to do a join or maybe make
a view of a view that would allow me to ultimately end up with…
StudnetID: 1
Name: John
HomePhone: 123-456-7890
WorkPhone: 123-456-7890
CellPhone:
Pager: 123-456-7890
Fax:
Memo: This is one student record.
Some students will have no phone number, some will have all 5 most will have
one or two. If possible I would like to do a setup like this in my database
to keep from having to have null fields for 4 phone numbers that the majorit
y
of records won’t have.
Thanks in advanced,
Nathan RoverHi
You can self join the tables, but that would reduce the flexibility of your
design e.g.
SELECT s.StudnetID. s.Name, a.PhoneNumber As HomePhone,
b.PhoneNumber As WorkPhone,
c.PhoneNumber As CellPhone
d.PhoneNumber As Pager
e.PhoneNumber As Fax
FROM Student s
LEFT JOIN StudentHasPhone a ON a.StudnetID = s.StudnetID AND a.PhoneTypeID
= ( SELECT PhoneTypeID FROM PhoneType WHERE PhoneType = 'Home phone' )
LEFT JOIN StudentHasPhone b ON b.StudnetID = s.StudnetID AND b.PhoneTypeID
= ( SELECT PhoneTypeID FROM PhoneType WHERE PhoneType = 'Work phone' )
LEFT JOIN StudentHasPhone c ON c.StudnetID = s.StudnetID AND c.PhoneTypeID
= ( SELECT PhoneTypeID FROM PhoneType WHERE PhoneType = 'Cell phone' )
LEFT JOIN StudentHasPhone d ON d.StudnetID = s.StudnetID AND d.PhoneTypeID
= ( SELECT PhoneTypeID FROM PhoneType WHERE PhoneType = 'Pager' )
LEFT JOIN StudentHasPhone e ON e.StudnetID = s.StudnetID AND e.PhoneTypeID
= ( SELECT PhoneTypeID FROM PhoneType WHERE PhoneType = 'Fax' )
The best solution is to process this at the client.
John
"NateDawg" wrote:

> I have three tables
> 1st table is Student
> StudnetID (pk)
> Other fields…
> 2nd table is PhoneType
> PhoneTypeID (pk)
> PhoneType
> 3rd table is StudentHasPhone
> SHPID (pk)
> StudnetID (fk)
> PhoneTypeID (fk)
> PhoneNumber
> PhoneType is an auxiliary table that has 5 records in it Home phone, Cell
> phone, Work phone, Pager, and Fax. Is there a way to do a join or maybe ma
ke
> a view of a view that would allow me to ultimately end up with…
> StudnetID: 1
> Name: John
> HomePhone: 123-456-7890
> WorkPhone: 123-456-7890
> CellPhone:
> Pager: 123-456-7890
> Fax:
> Memo: This is one student record.
> Some students will have no phone number, some will have all 5 most will ha
ve
> one or two. If possible I would like to do a setup like this in my databas
e
> to keep from having to have null fields for 4 phone numbers that the major
ity
> of records won’t have.
> Thanks in advanced,
> Nathan Rover
>