How to pull rows with missing corresponding data?

Question

So I want to create a function called MissingInfo.  I want to run that function against a table called tblAppointments.  Here is the structure of tblAppointments:

AppntID :Primary Key, int, Allow Nulls: No

MeetPlce:                   , smallint, Allow Nulls: Yes

MeetTime:                  , smallint, Allow Nulls: Yes

MeetChng:  , smallint, Allow Nulls: Yes

LblTab: ,smallint, Allow Nulls: Yes

BLblTab: ,smallint, Allow Nulls: Yes

CreateBy , nchar(40):  Allow Nulls: Yes

Date: ,datetime, Allow Nulls: Yes

So every time a client has a meeting, they are entered in the system with the following info.  Every one that has an ApptID, has a MeetPlce, and if the MeetPlc has a value, the MeetTime is also supposed to have a corresponding value.

Since MeetTime and MeetPlc are allowed null, some rows have a value for MeetPlc without the corresponding value for MeetTime, and MeetTime has a value of NULL.  I basically want to write a function that can pull only the rows that have a value for MeetPlc without a value for MeetTime(NULL) and like wise for BLblTab(NULL).  If LblTab has a value the BLblTab has to have a corresponding value as well and not NULL.

I only want to pull the rows for where MeetPlc, LblTab is Not NULL and  basically has a value and their corresponding columns MeetTime and BLblTab does NOT have a value or a value of NULL.

Is there a way to do this.  I have tried the where Not Exist (Select Null) but that brings up almost every row in the table since so many columns allow NULL values.  

So far I am stuck on this part, here is what I have so far

Select * from tblAppointments

Where Not Exists (Select Null) from tblAppointments.

As you probably can tell my query does not pull the specific information I need.  Any help would be greatly appreciated.  Thanking you in advance for your time!

CNM

---

CNmitchell

Answer 1

Hi nanowoman1,

Please see:

declare @tblAppointments table (AppntID int,MeetPlce int,MeetTime datetime,LblTab int,BLblTab int)

insert into @tblAppointments values (1,NULL,NULL,1,NULL)
insert into @tblAppointments values (2,1,NULL,1,NULL)
insert into @tblAppointments values (3,NULL,'19000101',NULL,1)
insert into @tblAppointments values (4,NULL,NULL,NULL,1)
insert into @tblAppointments values (5,1,'19000101',1,NULL)
insert into @tblAppointments values (6,1,'19000101',1,1)

select * from @tblAppointments 
where (MeetPlce is not null and MeetTime is null) or 
	(LblTab is not null and BLblTab is null)

If it's not what you want, please show us some sample data and your expected result.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Get or Request Code Sample from Microsoft
If you have any feedback, please tell us.

Answer 2

Please post your sample data, table structure and expected output, This will help us a lot.