crystalhelp
Programmer
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.
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.