CREATE TABLE demographic (subject_id CLOB, hadm_id CLOB, name CLOB, marital_status CLOB, age CLOB, dob CLOB, gender CLOB, language CLOB, religion CLOB, admission_type CLOB, days_stay CLOB, insurance CLOB, ethnicity CLOB, expire_flag CLOB, admission_location CLOB, discharge_location CLOB, diagnosis CLOB, dod CLOB, dob_year CLOB, dod_year CLOB, admittime CLOB, dischtime CLOB, admityear CLOB)
CREATE TABLE lab (subject_id CLOB, hadm_id CLOB, itemid CLOB, charttime CLOB, flag CLOB, value_unit CLOB, label CLOB, fluid CLOB)
CREATE TABLE prescriptions (subject_id CLOB, hadm_id CLOB, icustay_id CLOB, drug_type CLOB, drug CLOB, formulary_drug_cd CLOB, route CLOB, drug_dose CLOB)
CREATE TABLE diagnoses (subject_id CLOB, hadm_id CLOB, icd9_code CLOB, short_title CLOB, long_title CLOB)
CREATE TABLE procedures (subject_id CLOB, hadm_id CLOB, icd9_code CLOB, short_title CLOB, long_title CLOB)

provide the number of patients whose admission type is emergency and diagnosis long title is other drugs and medicinal substances causing adverse effects in therapeutic use.
SELECT COUNT(DISTINCT demographic.subject_id) FROM demographic INNER JOIN diagnoses ON demographic.hadm_id = diagnoses.hadm_id WHERE demographic.admission_type = "EMERGENCY" AND diagnoses.long_title = "Other drugs and medicinal substances causing adverse effects in therapeutic use"
SELECT COUNT(DISTINCT "demographic"."subject_id") FROM "demographic" JOIN "diagnoses" ON "Other drugs and medicinal substances causing adverse effects in therapeutic use" = "diagnoses"."long_title" AND "demographic"."hadm_id" = "diagnoses"."hadm_id" WHERE "EMERGENCY" = "demographic"."admission_type"