JOIN problem

[crystalhelp]

The below sql is not bringing back the results I want. I have tried using joins, which I think are the answer, but obviously it still isn't working.

The Lookup table is suppose to bring back the different severity types ie) Catastrophic, Serious, Major, Minor. All the incidences in the Incident table are assigned a severity id, which links to the lookup table.

What I want brought back are all incident id's and their associated severity type, but still return the severity type even if there are not incidences assigned to it.

This particular sql (with the date specified) brings back:

Serious 13789
Major 123890
Minor 87825

but I want it to bring back:

Catastrophic
Serious 13789
Major 123890
Minor 87825

select LOOKUP.LOOKUP_NAME, INCIDENT.INCIDENT_ID
from LOOKUP, INCIDENT
where LOOKUP.LOOKUP_TYPE='SEVERITY'
AND LOOKUP.LOOKUP_VALUE= INCIDENT.INCIDENT_SEVERITY_ID
AND INCIDENT.INCIDENT_STATUS_ID <> 3.00
AND INCIDENT.OCCURANCE_DATE <TO_DATE ('02-01-2001 00:00:01', 'DD-MM-YYYY HH24:MI:SS')
AND INCIDENT.OCCURANCE_DATE >TO_DATE ('01-01-2001 00:00:01', 'DD-MM-YYYY HH24:MI:SS');

If what I want it to do can't be done, let me know. Thanks in advance.

 

[lewisp]

I think this should work

select LOOKUP.LOOKUP_NAME,
INCIDENT.INCIDENT_ID
from LOOKUP,
INCIDENT
where LOOKUP.LOOKUP_TYPE(+) = 'SEVERITY'
AND LOOKUP.LOOKUP_VALUE(+) = INCIDENT.INCIDENT_SEVERITY_ID
AND INCIDENT.INCIDENT_STATUS_ID <> 3.00
AND INCIDENT.OCCURANCE_DATE <TO_DATE ('02-01-2001 00:00:01', 'DD-MM-YYYY HH24:MI:SS')
AND INCIDENT.OCCURANCE_DATE >TO_DATE ('01-01-2001 00:00:01', 'DD-MM-YYYY HH24:MI:SS');

 

[crystalhelp]

I had tried that and tried it again to be sure, but it doesn't work. Thanks for replying. I have a feeling it has to do with the bottom 3 lines where the Incident table is joined to variables.

 

[DaPi]

I think the outer join should be the other way round - there are no Catastophic INCIDENT's, so INCIDENT will be missing for some LOOKUP's:

select LOOKUP.LOOKUP_NAME,
INCIDENT.INCIDENT_ID
from LOOKUP,
INCIDENT
where LOOKUP.LOOKUP_TYPE = 'SEVERITY'
AND LOOKUP.LOOKUP_VALUE = INCIDENT.INCIDENT_SEVERITY_ID(+)
AND (INCIDENT.INCIDENT_STATUS_ID <> 3.00 OR INCIDENT.INCIDENT_STATUS_ID IS NULL)
AND (INCIDENT.OCCURANCE_DATE <TO_DATE ('02-01-2001 00:00:01', 'DD-MM-YYYY HH24:MI:SS') OR INCIDENT.OCCURANCE_DATE IS NULL)
AND (INCIDENT.OCCURANCE_DATE >TO_DATE ('01-01-2001 00:00:01', 'DD-MM-YYYY HH24:MI:SS') OR INCIDENT.OCCURANCE_DATE IS NULL)
;
DaPi - no silver bullet

 

[crystalhelp]

You gave me hope, but it didn't work.

 

[lewisp]

My apologies. It was the wrong way round! However, for the outer join to work, ALL columns from INCIDENT must be outer-joined

select LOOKUP.LOOKUP_NAME,
INCIDENT.INCIDENT_ID
from LOOKUP,
INCIDENT
where LOOKUP.LOOKUP_TYPE = 'SEVERITY'
AND LOOKUP.LOOKUP_VALUE = INCIDENT.INCIDENT_SEVERITY_ID(+)
AND (INCIDENT.INCIDENT_STATUS_ID(+) <> 3.00 OR INCIDENT.INCIDENT_STATUS_ID(+) IS NULL)
AND (INCIDENT.OCCURANCE_DATE(+) <TO_DATE ('02-01-2001 00:00:01', 'DD-MM-YYYY HH24:MI:SS') OR INCIDENT.OCCURANCE_DATE(+) IS NULL)
AND (INCIDENT.OCCURANCE_DATE(+) >TO_DATE ('01-01-2001 00:00:01', 'DD-MM-YYYY HH24:MI:SS') OR INCIDENT.OCCURANCE_DATE(+) IS NULL);

 

[lewisp]

I'll get this right in a minute!!

select LOOKUP.LOOKUP_NAME,
INCIDENT.INCIDENT_ID
from LOOKUP,
INCIDENT
where LOOKUP.LOOKUP_TYPE = 'SEVERITY'
AND LOOKUP.LOOKUP_VALUE = INCIDENT.INCIDENT_SEVERITY_ID(+)
AND INCIDENT.INCIDENT_STATUS_ID(+) <> 3.00
AND INCIDENT.OCCURANCE_DATE(+) <TO_DATE ('02-01-2001 00:00:01', 'DD-MM-YYYY HH24:MI:SS')
AND INCIDENT.OCCURANCE_DATE(+) >TO_DATE ('01-01-2001 00:00:01', 'DD-MM-YYYY HH24:MI:SS');

 

[crystalhelp]

Yeah! Thank you very much lewisp. I was starting to think I had tried all the join combinations, but you hit the nail on the head.

 

[DaPi]

Hmmmmm. Don't see it . . . . sorry.
I would try starting with:

select LOOKUP.LOOKUP_NAME,
INCIDENT.INCIDENT_ID
from LOOKUP,
INCIDENT
where LOOKUP.LOOKUP_TYPE = 'SEVERITY'
AND LOOKUP.LOOKUP_VALUE = INCIDENT.INCIDENT_SEVERITY_ID(+)
and LOOKUP.LOOKUP_NAME = 'Catastrophic';

When that works, try adding further conditions.

(BTW, it would perhaps be better to have
OR INCIDENT.INCIDENT_SEVERITY_ID IS NULL
rather than my first proposal for the OR's - just in case you have NULL's in the other columns tested on). DaPi - no silver bullet

 

[DaPi]

Looks like you fixed it while I was typing my last post! DaPi - no silver bullet