If statement in Stored Procedures help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

If statement in Stored Procedures help

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

Posted - 2008-06-23 : 15:52:25

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

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

Posted - 2008-06-24 : 15:03:53

You don't need constraints (foreign keys, primary keys, or unique keys) to use joins.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

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

Posted - 2008-06-24 : 19:38:45

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
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!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources