unfortunately, I have no control over the data schema.  I inherited this database and am in not in an authority to make any changes.  I do, however, agree with you.

Thanks...

Jonathan,

Thank you.  It has been a long, hard 3 weeks trying to get this to work.  It looks like your code did it. 

Thank you again.

Tina

Here are a small version of the tables.  These will cover the basics of my SQL

Create table event

(event_id int,

event_number char(25),

dttm_rcd_added char(14))

create table person_involved

(event_id int,

pi_eid int,

pi_type_code int)

create table entity

(entity_id...

Both of the last two solutions gives me the visitors with sytem_table_name = PHYSICIANS.

This sql resulted is eliminating those records with a patient AND a visitor. 

The results were records with just patients.  I then switched the code for visitor and patient, putting visitor...

That took care of the error.   Now the results are no records.  I know that there are at least 7 records that should appear.

I receive an error message that says "invalid column name event_id

1.  correct...none of the _id can be null.

2.  Yes, it's a typo.  Is should be PHYSICIANS.  Also, I have tried UPPER (for case-sensitive) and Trim (for leading blanks).  It didn't...

Event:

Event_number, event_id

Person_Involved:

Event_id, pi_eid, pi_type_code

Entity:

entity_id (=pi_eid), last_name, first_name, table_id

Glossary:

table_id, system_table_name

Event.event_id = Person_involved.event_id

Person_involved.pi_eid = Entity.entity_id

Entity.table_id = Glossary.table_id

Person_involved can be patient, visitor, witness, physician, other, etc

I need one column for patient and one column...