SQL query multiple records

[tpoettker]

I have two tables, event and person_involved. Person_involved can have more than one record to one event record. Person_involved table could be (not necessarily will be) patient, witness, physician, other, etc.

I need to create a view that would display the record in event table and the record(s) in person_involved. I need one record that would list the event, patient, physician and witness. My current SQL statement results in three records for my one event.

SELECT event.a, event.b, Patient.a, Patient.b, Physician.a,
Physician.b, Witness.a, Witness.b
FROM Event, Person_Involved as Patient, Person_Involved as
Physician, Person_Involved as Witness
WHERE event.event_id = Patient.event_id
AND event.event_id = Physician.event_id
AND event.event_id = Witness.event_id

 

[swampBoogie]

How is the Person_Involved table defined? Is there a tye field to distinguish each type of person?

 

[tpoettker]

yes there is. one field, PI_type.

 

[PHV]

You have to do 3 outer joins, one for each PI_type=

Hope This Help
PH.

 

[tpoettker]

I've tried what I think you mean and the results are still more than one record. Can you post an example of what you mean. Here's what I tried:

SELECT EVENT.*, Patient.*, Physician.*
FROM EVENT FULL OUTER JOIN
PERSON_INVOLVED Patient ON
EVENT.EVENT_ID = Patient.EVENT_ID FULL OUTER JOIN
PERSON_INVOLVED Physician ON
Patient.pi_type_code = Physician.pi_type_code

 

[PHV]

add a where PI-Type= on each join

Hope This Help
PH.