Puzzling SQL
-
I have a very complicated SQL or at least it is to me the newbie.
I have 4 tables -- EVENT, ENTITY, PERSON_INVOLVED, GLOSSARY. The EVENT table is the main table. Each EVENT can have one or more PERSON_INVOLVED record. Each PERSON_INVOLVED record has one ENTITY record.
I need a view of the event, person_involved as patient, person_involved as visitor. Patient has the Pi_type_code of 238. Visitor has the pi_type_code of 240 AND glossary.system_table_name <> 'PHYSICIAN'
Here's the lastest SQL I have tried:
SELECT event.event_number, event.dttm_rcd_added,
EnPatient.first_name + ' ' + Enpatient.last_name AS PatientName,
EnVisitor.first_name + ' ' + Envisitor.last_name AS Visitor,
piVisitor.pi_type_code, system_table_name
FROM EVENT FULL OUTER JOIN
Person_involved AS PIPatient ON
(event.event_id = PIPatient.event_id AND
piPatient.pi_type_code = 238) FULL OUTER JOIN
entity AS EnPatient ON
piPatient.pi_eid = EnPatient.entity_id FULL OUTER JOIN
person_involved AS PIVisitor ON
(event.event_id = PiVisitor.event_id AND
PiVisitor.pi_type_code = 240) FULL OUTER JOIN
entity AS EnVisitor ON
PiVisitor.pi_eid = EnVisitor.entity_id FULL OUTER JOIN
glossary ON
(glossary.table_id = EnVisitor.entity_table_id AND
pivisitor.pi_type_code = 240 AND
glossary.system_table_name <> 'PHYSICIANS')
WHERE piPatient.pi_type_code = 238 AND
SUBSTRING(event.dttm_rcd_added, 1, 8)
>= CONVERT(char(26), DATEADD(dd, - 1, GETDATE()), 112) OR
(piVisitor.pi_type_code = 240 AND
glossary.system_table_name <> 'PHYSICIANS') AND
SUBSTRING(event.dttm_rcd_added, 1, 8)
>= CONVERT(char(26), DATEADD(dd, - 1, GETDATE()), 112)
This SQL results in
EV088994 20040225023929 Joshua Harmon MattGoldstein 240
EV088995 20040225031156 SHARON DENNISON
EV088997 20040225031507 RUTH STUMPF
EV088999 20040225031801 ROBERT FREEMAN
EV089000 20040225031925 DONALD STANKUS
The problem is MattGoldstein 240 has the system_Table_name of PHYSICIAN. I have tried other SQL's but this is the closest to what I need.
Any ideas how to keep the Matts out of the results?
Thanks
-
Pat,
I did not look at it very closely, but I see that you have an OUTER JOIN on [glossary], with an ON condition that removes PHYSICIANS. Since and PHYSICIANS records will NOT be part of the JOIN [glossary], your WHERE clause filtering of the [glossary] table will not have effect.
I suggest you change ...
FULL OUTER JOIN
glossary ON
(glossary.table_id = EnVisitor.entity_table_id AND
pivisitor.pi_type_code = 240 AND
glossary.system_table_name <> 'PHYSICIANS')
to
FULL OUTER JOIN
glossary ON glossary.table_id = EnVisitor.entity_table_id
This will let your WHERE condition have effect.
Or you could use a JOIN instead of OUTER JOIN on [glossary]. I do not know if you require an OUTER JOIN.
hope this helps.
Once you understand the BITs, all the pieces come together
-
I am not very sure you need full outer join for your query ?
Can you post something about your tables DDL or relations?
* Noel -
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 for visitor.
-
Ok before I procced 3 more questions?
(sorry)1.NONE of the _Id can be Null, right?
2. on your question you said that a visitor is someone with 240 and glossary.system_table_name <> 'PHYSICIAN' but you are filtering by 'PHYSICIANS' is that a typo?
3. How would you handle the case where a patient has more than 1 visitor ?
* Noel -
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 affect anything.
3. It would be ok to have more than one record if there are more than 1 visitor. This sql is being used to create a view that will be used in Crystal Report. I can "group" the visitors in crystal reports.
-
Ok I don't have Sample Data
Can you check if this is what you want:SELECT
event.event_number
, event.dttm_rcd_added
, Patients.PatientName
, Visitors.VisitorName
, Visitors.system_table_name
FROM
Event
JOIN
(SELECT event_id int, Entity.first_name + ' ' + Entity.last_name AS PatientName
FROM Person_involved
JOIN
Entity
ON Entity.entity_id = Person_involved.pi_eid
WHERE Person_involved.pi_type_code = 238) Patients
ON Event.event_id = Patients.event_id
JOIN
(SELECT event_id , Entity.first_name + ' ' + Entity.last_name AS VisitorName, system_table_name
FROM Person_involved
JOIN
Entity
ON Entity.entity_id = Person_involved.pi_eid
JOIN
glossary
ON Glossary.table_id = Entity.table_id
WHERE Person_involved.pi_type_code = 240 AND glossary.system_table_name <> 'PHYSICIANS') AS Visitors
ON Event.event_id = Visitors.event_id
WHERE
SUBSTRING(event.dttm_rcd_added, 1, 8)
>= CONVERT(char(26), DATEADD(dd, - 1, GETDATE()), 112)
* Noel -
I receive an error message that says "invalid column name event_id
-
SELECT
event.event_number
, event.dttm_rcd_added
, Patients.PatientName
, Visitors.VisitorName
, system_table_name
FROM
Event
JOIN
(SELECT event_id, Entity.first_name + ' ' + Entity.last_name AS PatientName
FROM Person_involved
JOIN
Entity
ON Entity.entity_id = Person_involved.pi_eid
WHERE Person_involved.pi_type_code = 238) Patients
ON Event.event_id = Patients.event_id
JOIN
(SELECT Person_involved.event_id , Entity.first_name + ' ' + Entity.last_name AS VisitorName, system_table_name
FROM Person_involved
JOIN
Entity
ON Entity.entity_id = Person_involved.pi_eid
JOIN
glossary
ON Glossary.table_id = Entity.table_id
WHERE Person_involved.pi_type_code = 240 AND glossary.system_table_name <> 'PHYSICIANS') AS Visitors
ON Event.event_id = Visitors.event_id
WHERE
SUBSTRING(event.dttm_rcd_added, 1, 8)
>= CONVERT(char(26), DATEADD(dd, - 1, GETDATE()), 112)
I had and extra "int" from the table Definition (sorry)
* Noel -
That took care of the error. Now the results are no records. I know that there are at least 7 records that should appear.
-
Perfect,
Assuming that there are patients without Visitors all you need is to convert the one join to left outer and life will be good
Here you go!
SELECT
event.event_number
, event.dttm_rcd_added
, Patients.PatientName
, Visitors.VisitorName
, system_table_name
FROM
Event
JOIN
(SELECT event_id, Entity.first_name + ' ' + Entity.last_name AS PatientName
FROM Person_involved
JOIN
Entity
ON Entity.entity_id = Person_involved.pi_eid
WHERE Person_involved.pi_type_code = 238) Patients
ON Event.event_id = Patients.event_id
LEFT OUTER JOIN
(SELECT Person_involved.event_id , Entity.first_name + ' ' + Entity.last_name AS VisitorName, system_table_name
FROM Person_involved
JOIN
Entity
ON Entity.entity_id = Person_involved.pi_eid
JOIN
glossary
ON Glossary.table_id = Entity.table_id
WHERE Person_involved.pi_type_code = 240 AND glossary.system_table_name <> 'PHYSICIANS') AS Visitors
ON Event.event_id = Visitors.event_id
WHERE
SUBSTRING(event.dttm_rcd_added, 1, 8)
>= CONVERT(char(26), DATEADD(dd, - 1, GETDATE()), 112)
* Noel -
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 before patient, then the results were records with just visitor.
I feel like somewhere needs to be an "OR" statement.
-
Due to the results you said then I would change the first Join to LEFT OUTER Like:
SELECT
event.event_number
, event.dttm_rcd_added
, Patients.PatientName
, Visitors.VisitorName
, system_table_name
FROM
Event
LEFT OUTER JOIN
(SELECT event_id, Entity.first_name + ' ' + Entity.last_name AS PatientName
FROM Person_involved
JOIN
Entity
ON Entity.entity_id = Person_involved.pi_eid
WHERE Person_involved.pi_type_code = 238) Patients
ON Event.event_id = Patients.event_id
LEFT OUTER JOIN
(SELECT Person_involved.event_id , Entity.first_name + ' ' + Entity.last_name AS VisitorName, system_table_name
FROM Person_involved
JOIN
Entity
ON Entity.entity_id = Person_involved.pi_eid
JOIN
glossary
ON Glossary.table_id = Entity.table_id
WHERE Person_involved.pi_type_code = 240 AND glossary.system_table_name <> 'PHYSICIANS') AS Visitors
ON Event.event_id = Visitors.event_id
WHERE
SUBSTRING(event.dttm_rcd_added, 1, 8)
>= CONVERT(char(26), DATEADD(dd, - 1, GETDATE()), 112)
It is a little hard without some sample data. can you post some samples?
* Noel -
SELECT e.Event_Number, e.DtTm_Rcd_Added, ep.First_Name + ' ' + ep.Last_Name PatientName,
ev.First_Name + ' ' + ev.Last_Name Vistor, v.Pi_Type_Code, g.System_Table_Name
FROM Event e
JOIN Person_Involved p ON e.Event_Id = p.Event_Id AND p.Pi_Type_Code = 238
JOIN Entity ep ON p.Pi_Eid = ep.Entity_Id
LEFT JOIN Person_Involved v ON e.Event_Id = v.Event_Id AND v.Pi_Type_Code = 240
LEFT JOIN Glossary g ON v.Entity_Table_Id = g.Table_Id
LEFT JOIN Entity ev ON v.Pi_Eid = ev.Entity_Id
WHERE e.DtTm_Rcd_Added >= CONVERT(char(8),GETDATE() - 1,112)
AND (g.System_Table_Name <> 'PHYSICIANS' OR g.System_Table_Name IS NULL)
--Jonathan -
Both of the last two solutions gives me the visitors with sytem_table_name = PHYSICIANS.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply