|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
April 26th, 2005, 03:34 PM
|
|||
|
|||
|
left join query
I have the following query at the bottom of the page that list incidents that meets a criteria and the incidents are not listed in a medical table.
It works fine. I need to add the the fields in blue but I can't figure out the join synatx for the other three fields The joins to be added are Code:
TBL_INCIDENT.INCIDENT_TYPE = TBL_INCIDENT_TYPE.INCIDENT_TYPE_ID AND TBL_INCIDENT.INCIDENT_LOCATION = TBL_LOCATION.LOCATION_ID AND TBL_INCIDENT.INCIDENT_PROGRAM = TBL_PROGRAM.PROGRAM_ID Any help would be great. Thanks Fr. Robert Code:
SELECT TBL_INCIDENT.INCIDENT_ID, TBL_INCIDENT.REPORT_DATE, TBL_INCIDENT.INCIDENT_DATE, CAST(TBL_INCIDENT.INCIDENT_TIME AS VARCHAR(32)), TBL_INCIDENT.INCIDENT_LOCATION, TBL_LOCATION.LOCATION_DESC, TBL_INCIDENT.INCIDENT_PLACE, TBL_INCIDENT.INCIDENT_PROGRAM, TBL_PROGRAM.PROGRAM_DESC, TBL_INCIDENT.INCIDENT_TYPE, TBL_INCIDENT_TYPE.INCIDENT_TYPE_DESC, TBL_INCIDENT.OTHER_PERSONS, TBL_INCIDENT.OTHER_WITNESSES, TBL_INCIDENT.INCIDENT_DESC, TBL_INCIDENT.IMMEDIATE_ACTION, TBL_INCIDENT.INITIAL_NAME, TBL_INCIDENT.INITIAL_TITLE, TBL_INCIDENT.INITIAL_SIGNATURE, TBL_INCIDENT.INITIAL_ENTRY_DATE FROM (TBL_INCIDENT LEFT JOIN TBL_MEDICAL ON TBL_INCIDENT.INCIDENT_ID = TBL_MEDICAL.INCIDENT_ID) WHERE TBL_MEDICAL.INCIDENT_ID IS NULL AND TBL_INCIDENT.INCIDENT_TYPE<=4
|
|
#3
May 7th, 2005, 05:13 AM
|
|||
|
|||
|
can you post how you did it?
|
|
#4
May 20th, 2005, 03:30 PM
|
|||
|
|||
|
Quote:
Code:
SELECT
TBL_INCIDENT.INCIDENT_ID,
EXTRACT(MONTH FROM TBL_INCIDENT.REPORT_DATE)||'-' || EXTRACT(DAY FROM TBL_INCIDENT.REPORT_DATE)||'-' || EXTRACT(YEAR FROM TBL_INCIDENT.REPORT_DATE) ,
EXTRACT(MONTH FROM TBL_INCIDENT.INCIDENT_DATE)||'-' || EXTRACT(DAY FROM TBL_INCIDENT.INCIDENT_DATE)||'-' || EXTRACT(YEAR FROM TBL_INCIDENT.INCIDENT_DATE) ,
CAST(TBL_INCIDENT.INCIDENT_TIME AS VARCHAR(32)),
TBL_INCIDENT.INCIDENT_LOCATION,
TBL_LOCATION.LOCATION_DESC,
TBL_INCIDENT.INCIDENT_PLACE,
TBL_INCIDENT.INCIDENT_PROGRAM,
TBL_PROGRAM.PROGRAM_DESC,
TBL_INCIDENT.INCIDENT_TYPE,
TBL_INCIDENT_TYPE.INCIDENT_TYPE_DESC,
TBL_INCIDENT.OTHER_PERSONS,
TBL_INCIDENT.OTHER_WITNESSES,
TBL_INCIDENT.INCIDENT_DESC,
TBL_INCIDENT.IMMEDIATE_ACTION,
TBL_INCIDENT.INITIAL_NAME,
TBL_INCIDENT.INITIAL_TITLE,
TBL_INCIDENT.INITIAL_SIGNATURE,
EXTRACT(MONTH FROM TBL_INCIDENT.INITIAL_ENTRY_DATE)||'-' || EXTRACT(DAY FROM TBL_INCIDENT.INITIAL_ENTRY_DATE)||'-' || EXTRACT(YEAR FROM TBL_INCIDENT.INITIAL_ENTRY_DATE)
FROM
TBL_INCIDENT_PEOPLE
INNER JOIN TBL_CLIENTS ON TBL_INCIDENT_PEOPLE.PERSON_ID = TBL_CLIENTS.CLIENT_ID
LEFT JOIN TBL_PROGRAM_FOLLOWUP ON TBL_INCIDENT_PEOPLE.INCIDENT_ID = TBL_PROGRAM_FOLLOWUP.INCIDENT_ID
INNER JOIN TBL_INCIDENT ON TBL_INCIDENT.INCIDENT_ID = TBL_INCIDENT_PEOPLE.INCIDENT_ID
INNER JOIN TBL_INCIDENT_TYPE ON TBL_INCIDENT.INCIDENT_TYPE = TBL_INCIDENT_TYPE.INCIDENT_TYPE_ID
INNER JOIN TBL_LOCATION ON TBL_INCIDENT.INCIDENT_LOCATION = TBL_LOCATION.LOCATION_ID
INNER JOIN TBL_PROGRAM ON TBL_INCIDENT.INCIDENT_PROGRAM = TBL_PROGRAM.PROGRAM_ID
WHERE
TBL_PROGRAM_FOLLOWUP.INCIDENT_ID IS NULL
;
|
|
| Viewing: Dev Shed Forums > Databases > Firebird SQL Development > left join query |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
