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

Joined table -- display in datagrid

Ok here goes. I have 3 tables, one holds case info, the 2nd holds possible outcome on the charges, and they're joined on a 3rd table (CaseOutComes). With me so far? Easy stuff, now for the hard part.

Since there's a very common possiblitly that the Case has multiple charges, we need to track those, and therefore, display them on a datagrid or some other control. I want the user to be able to edit the info and have X number of dropdowns pertaining to how many ever charges are on the case. I can get the query to return the rows no sweat, but ...merging them into 1 record (1 row) with mutiple drops is seeming impossible -- I thought about using a placeholder and added the controls that way, but it was not in agreement with what I was trying to tell it.

Any ideas on how to attack this?

You are saying you have the query working. Are you having problem displaying the data? If so I can move the post to Datagrid section where you have a better chance of receiving help.|||

ndinakar:

Are you having problem displaying the data?

I think its more sql based than grid based -- might be both (probably is). Here's an example.

Table - Cases : caseID (pk), CaseNumber, Notes
Table - Outcomes : outcomeID (pk), outcome
Table - CasesOutcome : caseID (fk), outcomeID (fk)

Data - Cases :
caseID : 1
CaseNumber : 2007xx45
Notes : (empty)

Data - Outcomes :
outcomeID : 1
outcome : guilty as charged by judge

outcomeID : 2
outcome : pled guilty to felony assault charge

outcomeID : 3
outcome : pled guilty to felony theft charge

Data - CasesOutcome:
caseID : 1
outcomeID : 2

caseID : 1
outcomeID : 3

When this is all said and done, a grid is generated with two rows, repeating the data (shows the case number twice) which is not really desired -- the outcome however is. Ideally the two of those items would show up under the gridview in 1 column (when being edited, they change to dropdowns) -- but I'm not sure if this is even possible.

|||Can you post the query you have, and the expected result.

Join not returning records if one missing.

How do I set up my query to get data from a 2nd file when there may not be
any data?
For example, the following select just gets some data from the Position
table. The Category Description is in the JobCategory Table. I have the
CategoryID in the Position table.
Select PositionID,JobTitle,Category
from Position p
Join JobCategory j on p.categoryCode = j.categoryCode
where PositionID = 54
This works fine if the categoryCode happens to be in both tables. It may
not be there as it may be 0 (or null) if a code had not been chosen.
What I want to have happen is just have Category be blank if there is no
matching record.
What happens here is that I don't get the Position record either.
Thanks,
TomTry:
Select PositionID,JobTitle,Category
from Position p
Left Join JobCategory j on p.categoryCode = j.categoryCode
where p.PositionID = 54
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:u4uWz2gRFHA.3296@.TK2MSFTNGP15.phx.gbl...
How do I set up my query to get data from a 2nd file when there may not be
any data?
For example, the following select just gets some data from the Position
table. The Category Description is in the JobCategory Table. I have the
CategoryID in the Position table.
Select PositionID,JobTitle,Category
from Position p
Join JobCategory j on p.categoryCode = j.categoryCode
where PositionID = 54
This works fine if the categoryCode happens to be in both tables. It may
not be there as it may be 0 (or null) if a code had not been chosen.
What I want to have happen is just have Category be blank if there is no
matching record.
What happens here is that I don't get the Position record either.
Thanks,
Tom|||Witout DDL for the tables, it's hard to be sure, but I think what you are
trying to do would require an Outer Join. In an Outer Join, all the records
from one side will be produced, even if there's no match from the other side
on the Join condidtions
Select PositionID,JobTitle,Category
From Position p
Left Outer Join JobCategory j
On j.categoryCode = p.categoryCode
Where PositionID = 54
"tshad" wrote:

> How do I set up my query to get data from a 2nd file when there may not be
> any data?
> For example, the following select just gets some data from the Position
> table. The Category Description is in the JobCategory Table. I have the
> CategoryID in the Position table.
> Select PositionID,JobTitle,Category
> from Position p
> Join JobCategory j on p.categoryCode = j.categoryCode
> where PositionID = 54
> This works fine if the categoryCode happens to be in both tables. It may
> not be there as it may be 0 (or null) if a code had not been chosen.
> What I want to have happen is just have Category be blank if there is no
> matching record.
> What happens here is that I don't get the Position record either.
> Thanks,
> Tom
>
>|||Dear tshad,
Try following .....
-- If U want all records from Position
Select P.PositionID,J.JobTitle,J.Category from Position P
Left Join JobCategory J
on P.categoryCode = J.categoryCode
where P.PositionID = <<UrInput Value>>
-- If U want all records from JobCategory
Select P.PositionID,J.JobTitle,J.Category from Position P
Right Join JobCategory J
on P.categoryCode = J.categoryCode
where P.PositionID = <<UrInput Value>>
With Regards,
Rakesh Ranjan
Mail me on -- rakesh.ranjan@.3i-infotech.com
"tshad" wrote:

> How do I set up my query to get data from a 2nd file when there may not be
> any data?
> For example, the following select just gets some data from the Position
> table. The Category Description is in the JobCategory Table. I have the
> CategoryID in the Position table.
> Select PositionID,JobTitle,Category
> from Position p
> Join JobCategory j on p.categoryCode = j.categoryCode
> where PositionID = 54
> This works fine if the categoryCode happens to be in both tables. It may
> not be there as it may be 0 (or null) if a code had not been chosen.
> What I want to have happen is just have Category be blank if there is no
> matching record.
> What happens here is that I don't get the Position record either.
> Thanks,
> Tom
>
>