| Author |
Topic |
|
Phantom_skulcave
Starting Member
3 Posts |
 Posted - 2008-06-23 : 15:43:08
|
Hi all. This has kept me up all night! I'm trying to populate a datgrid with multiple tables with relating keys in each table.
For ease, I've made the databases hopefully understandable.
DoctorsDB - (ID, Name, Phone)
FriendsDB - (ID, Name, Phone)
PetsDB - (ID, Name)
PeopleISawTodayDB - (MyID, MyName, DocID, FriendID, PetsID)
Now I need to generate a datagrid with the following details:
MyID, MyName, Doc's Name, Friend's Name, Pet's Name.
I have some success with the following code:
PROCEDURE dbo.PeopleISawTodayDB
@ID as Int
AS
BEGIN
SELECT
PeopleISawTodayDB.MyID, PeopleISawTodayDB.MyName, PeopleISawTodayDB.DocID, PeopleISawTodayDB.FriendID, PeopleISawTodayDB.PetID, DoctorsDB.ID, DoctorsDB.Name, FriendDB.ID, FriendDB.Name, PetDB.ID, PetDB.Name
FROM
PeopleISawTodayDB INNER JOIN
DoctorDB ON PeopleISawTodayDB.DocID = DoctorDB.ID INNER JOIN
FriendDB ON PeopleISawTodayDB.FriendID = FriendDB.ID INNER JOIN
PetDB ON PeopleISawTodayDB.PetID = Pet.ID
WHERE PeopleISawTodayDB.JobID=@ID
END
BUT it will only make a row appear if there are ID's in each of the PeopleISawTodayDB respective ID fields.
If I want to leave one blank (as in I didn't see that person that day), I would like it to still find the other details and populate the datagrid.
Can anyone lend a coding hand???? Cheers |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Phantom_skulcave
Starting Member
3 Posts |
Posted - 2008-06-24 : 14:41:08
|
Absolute legend!!!! Thanks, Tara.
Only one more thing - is it possible to return a result if one of the foreign keys is empty?
For example, it works beautifully if I have all foreign keys in the PeopleISawTodayDB, but I don't necessarily want it to be there unless it's pertinent. And if a foreign key is missing, it doesn't return ANY results.
Cheers!
quote:
Originally posted by tkizer
Use an OUTER JOIN (preferably LEFT) instead of INNER JOIN. Check BOL for the differences between the join types.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Subscribe to my blog
|
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-06-24 : 19:37:00
|
quote:
Originally posted by Phantom_skulcave
Absolute legend!!!! Thanks, Tara.
Only one more thing - is it possible to return a result if one of the foreign keys is empty?
For example, it works beautifully if I have all foreign keys in the PeopleISawTodayDB, but I don't necessarily want it to be there unless it's pertinent. And if a foreign key is missing, it doesn't return ANY results.
Cheers!
quote:
Originally posted by tkizer
Use an OUTER JOIN (preferably LEFT) instead of INNER JOIN. Check BOL for the differences between the join types.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Subscribe to my blog
You need to change the query to have
Parent_table LEFT OUTER JOIN child_table.....
Madhivanan
Failing to plan is Planning to fail |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-06-24 : 19:43:11
|
quote:
Originally posted by tkizer
I through in the "preferably LEFT" for Jeff's sake. 
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Subscribe to my blog
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Phantom_skulcave
Starting Member
3 Posts |
Posted - 2008-06-24 : 19:49:40
|
Thanks guys. With your help, I got it working and working well.
Cheers and drinks all around!!! |
|
|
|
|
If statement in Stored Procedures help