Puzzling SQL

[tpoettker]

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

 

[SQLBill]

Is there a chance your database is case-sensitive?

Test this:

UPPER(glossary.system_table_name) <> 'PHYSICIANS'

That will compare glossary.system_table_name in UPPER CASE letters to PHYSICIANS. If it works, that means your database is case-sensitive and PHYSICIANS in the database is probably in lower case or mixed case.

-SQLBill

 

[tpoettker]

I tested your thought. It didn't change things. I didn't think my database was case-sensitive but you never know.

 

[jms8222]

Could it be as simple as you have in your query:
glossary.system_table_name <> 'PHYSICIANS'
and it should be:
glossary.system_table_name <> 'PHYSICIAN'

 

[tpoettker]

Unfortunately not. The value is PHYSICIANS.

 

[jms8222]

I'm trying to make sense of your where clause. You've got the one statement repeated twice (first on line 21, then on line 25):

SUBSTRING(event.dttm_rcd_added, 1, 8)
>= CONVERT(char(26), DATEADD(dd, - 1, GETDATE()), 112)

Could you need to move the end paren for your OR from after the word 'PHYSICIANS' to after this statement is made the second time??

 

[MDXer]

Could Matt Coldstein 240 have a trailing space or leading white space?

LTrim(Rtrim(glossary.system_table_name)) <> 'PHYSICIANS'

&quot;Shoot Me! Shoot Me NOW!!!&quot;
- Daffy Duck

 

[tpoettker]

The code in my SQL twice is to establish "yesterday" date. My criteria is

pi_type_code = 238 and events added "yesterday"
OR
pi_type_code = 240 and glossary.system_table_name <> 'PHYSICIANS' and events added yesterday

When I didn't add the "yesterday" criteria to both pi_type_code, I got really weird results.


I tried the LTrim(xxx) -- results didn't change.

 

[zhavic]

You have too much conditions in that simple query.
If you have condition in ON clause of JOINs, you don't need it
in WHERE clause.

Try to experiment with more simple version:

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 
	INNER JOIN Person_involved AS PIPatient ON (event.event_id = PIPatient.event_id AND piPatient.pi_type_code = 238) 
	INNER JOIN entity AS EnPatient ON piPatient.pi_eid = EnPatient.entity_id 
	LEFT JOIN person_involved AS PIVisitor ON (event.event_id = PiVisitor.event_id AND PiVisitor.pi_type_code = 240) 
	LEFT JOIN entity AS EnVisitor ON PiVisitor.pi_eid = EnVisitor.entity_id 
	LEFT JOIN glossary ON (glossary.table_id = EnVisitor.entity_table_id)
WHERE SUBSTRING(event.dttm_rcd_added, 1, 8) >= CONVERT(char(26), DATEADD(dd, - 1, GETDATE()), 112)
	AND ( PiVisitor.event_id IS NULL OR glossary.system_table_name <> 'PHYSICIANS' )

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[tpoettker]

This didn't work either. It completely eliminated the records that had a visitor. The visitors should be present if they have the code of 240 and <> Physicians.

 

[jms8222]

If you want to establish yesterday's date to pi_type_code=240, then you need to move a paren.

Change it from:
(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)

To:
(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))

See if that helps your results any.

 

[tpoettker]

The results didn't change.

 

[zhavic]

This need only some change in JOINS, so try this:
( the same but with all LEFT joins )

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
    LEFT JOIN Person_involved AS PIPatient ON (event.event_id = PIPatient.event_id AND piPatient.pi_type_code = 238)
    LEFT JOIN entity AS EnPatient ON piPatient.pi_eid = EnPatient.entity_id
    LEFT JOIN person_involved AS PIVisitor ON (event.event_id = PiVisitor.event_id AND PiVisitor.pi_type_code = 240)
    LEFT JOIN entity AS EnVisitor ON PiVisitor.pi_eid = EnVisitor.entity_id
    LEFT JOIN glossary ON (glossary.table_id = EnVisitor.entity_table_id)
WHERE SUBSTRING(event.dttm_rcd_added, 1, 8) >= CONVERT(char(26), DATEADD(dd, - 1, GETDATE()), 112)
    AND ( PiVisitor.event_id IS NULL OR glossary.system_table_name <> 'PHYSICIANS' )

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[tpoettker]

Ok, now I am getting the visitor information but the patient information is NULL.

 

[zhavic]

hmmmm,

which type and format is your column 'event.dttm_rcd_added' ?

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[tpoettker]

event.dttm_rcd_added is a character field (ccyymmddhhmmss)

 

[rute67]

You can also try

where Enpatient.last_name not in (select last_name from physicians)

 

[tektipdjango]

Try this solution with derived tables :
----------------------------------------------
SELECT
event.event_Id,
event.dttm_rcd_added,
piPatient.first_Name,
piPatient.last_Name,
PiVisitor.pi_type_code,
PiVisitor.system_table_name,
PiVisitor.first_Name,
PiVisitor.last_Name
FROM
(
event
LEFT JOIN
(
SELECT
person_involved.event_Id,
first_Name,
last_Name
FROM
person_involved
RIGHt JOIN
entity
ON person_involved.pi_eid = entity.entity_Id
WHERE person_involved.pi_type_code=238
) AS
piPatient
ON event.event_number = piPatient.event_Id
)
LEFT JOIN
(
SELECT
event_Id,
pi_type_code,
system_table_name,
first_Name,
last_Name
FROM
(
person_involved AS person_involved2
RIGHT JOIN
entity AS entity2
ON person_involved2.pi_eid = entity2.entity_Id
)
LEFT JOIN
glossary
ON entity2.entity_tableId = glossary.table_id
WHERE
person_involved2.pi_typecode=240
AND
glossary.system_table_name<>'physicians'
)AS
PiVisitor
ON event.event_number = PiVisitor.event_Id
-------------------------------------------------

Let me know if successful

django
bug exterminator
tips'n tricks addict

 

[tektipdjango]

just check the names
physicians PHYSICIANS

and field names



django
bug exterminator
tips'n tricks addict

 

[tpoettker]

Where the event had the appropriate visitor information, the patient name is null.