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 IntASBEGINSELECT PeopleISawTodayDB.MyID, PeopleISawTodayDB.MyName, PeopleISawTodayDB.DocID, PeopleISawTodayDB.FriendID, PeopleISawTodayDB.PetID, DoctorsDB.ID, DoctorsDB.Name, FriendDB.ID, FriendDB.Name, PetDB.ID, PetDB.NameFROM PeopleISawTodayDB INNER JOIN DoctorDB ON PeopleISawTodayDB.DocID = DoctorDB.ID INNER JOIN FriendDB ON PeopleISawTodayDB.FriendID = FriendDB.ID INNER JOIN PetDB ON PeopleISawTodayDB.PetID = Pet.IDWHERE PeopleISawTodayDB.JobID=@ID ENDBUT 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
You need to change the query to haveParent_table LEFT OUTER JOIN child_table.....MadhivananFailing 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
MadhivananFailing 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!!! |
 |
|
|