Joining multiple tables in a view.

I have three tables

1^st table is Student

StudnetID (pk)

Other fields…

2^nd table is PhoneType

PhoneTypeID (pk)

PhoneType

3^rd 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 majority of records won't have.

Thanks in advanced,

Nathan Rover

What you need is a View with UNION ALL but your tables must be UNION compatible which means same data type facing the same direction. Try the link below for sample code. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_30hj.asp

|||

You can join to the phone table multiple times, as follows:

SELECT StudentID, HP.PhoneNumber, WP.PhoneNumber, CP.PhoneNumber, Pg.PhoneNumber, Fx.PhoneNumber
FROM Student S
LEFT OUTER JOIN StudentHasPhone HP
ON S.StudentID = HP.StudentID
AND HP.PhoneTypeID = 1 --Home Phone
LEFT OUTER JOIN StudentHasPhone WP
ON S.StudentID = WP.StudentID
AND WP.PhoneTypeID = 2 --Work Phone
LEFT OUTER JOIN StudentHasPhone CP
ON S.StudentID = CP.StudentID
AND CP.PhoneTypeID = 3 --Cell Phone
LEFT OUTER JOIN StudentHasPhone Pg
ON S.StudentID = Pg.StudentID
AND Pg.PhoneTypeID = 4 --Pager
LEFT OUTER JOIN StudentHasPhone Fx
ON S.StudentID = Fx.StudentID
AND Fx.PhoneTypeID = 5 --Fax

BTW: StudentHasPhone is not a good table name. StudentPhone, or simply Phone, would be much better.

|||

Thanks, that was exactly what I was looking for… It worked perfect.

--Nathan

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
>