Hi, I have the folowing query:
SELECT cnv.cnv_reg_ans , gih.gih_dias , gih.gih_senha , pac.pac_nome , gih.gih_numero , gih.gih_atendente , gih.gih_qtde , pac.pac_mcnv , pac.pac_dt_valid , pac.pac_cartao_sus , psv.psv_nome , psv.psv_uf , psv.psv_crm , psv.psv_cpf , emp_a.emp_cgc , gih.gih_dthr_ini , cfg.cfg_ind_homecare , cap.cap_pac_reg , cap.cap_num , cap.cap_cid_cod , cap.cap_tiss_tipo_doenca , cap.cap_tiss_tempo_doenca , cap.cap_tiss_escala_doenca , cap.cap_tiss_tipo_acidente , cap.cap_int_carater , cap.cap_int_clinc_cir , cap.cap_int_hosp , gih.gih_dthr_fim , gih.gih_dthr_lib , gih.gih_obs , gih.gih_qtde_solic , ( SELECT pln.pln_cod_conv FROM pln WHERE pln.pln_cnv_cod = cap.cap_cnv_codAND pln.pln_cod = pac.pac_pln_cod AND pln.pln_cnv_cod = pac.pac_cnv ) as plano, ( SELECT pln.pln_nome FROM pln WHERE pln.pln_cnv_cod = cap.cap_cnv_cod AND pln.pln_cod = pac.pac_pln_cod AND pln.pln_cnv_cod = pac.pac_cnv ) as plano_nome, psv.psv_conselho , ( SELECT MIN ( d.esp_cbo_s ) FROM esp d, esm m WHERE m.esm_esp = d.esp_cod AND m.esm_default = 'S' AND m.esm_med = gih.gih_psv_solic ) as esp_medico_solic, emp_a.emp_nome_fantasia , emp_a.emp_cnes , emp_b.emp_cgc , emp_b.emp_nome_fantasia , emp_b.emp_cnes , ( SELECT o.emp_tiss_cod_oper FROM emp o WHERE o.emp_cod = cnv.cnv_emp_cod ) as emp_cod_oper, ( SELECT cid.cid_nome FROM cid WHERE cid.cid_cod = cap.cap_cid_cod ) as cid_nome, cap.cap_obs , cnv.cnv_nome , ( SELECT cle.cle_cod_tiss FROM cle WHERE cle_cod = gih.gih_cle_cod ) as padrao_autorizado, cap.cap_tiss_indic_clinica , ( SELECT min ( pdg.pdg_cid ) FROM pdg WHERE pdg.pdg_pac = gih.gih_pac_regAND pdg.pdg_hsp = gih.gih_hsp_num AND pdg.pdg_hsp > 0 AND pdg.pdg_principal <> 'S' ) as cid_cod_2, ( SELECT max ( pdg.pdg_cid ) FROM pdg WHERE pdg.pdg_pac = gih.gih_pac_regAND pdg.pdg_hsp = gih.gih_hsp_num AND pdg.pdg_hsp > 0 AND pdg.pdg_principal <> 'S' ) as cid_cod_3, ( SELECT min ( pdg.pdg_cid ) FROM pdg WHERE pdg.pdg_pac = gih.gih_pac_regAND pdg.pdg_hsp = gih.gih_hsp_num AND pdg.pdg_hsp > 0 AND pdg.pdg_principal <> 'S' and pdg_cid not in ( SELECT min ( b.pdg_cid ) FROM pdg b WHERE b.pdg_pac= gih.gih_pac_reg AND b.pdg_hsp = gih.gih_hsp_num AND b.pdg_hsp > 0 AND b.pdg_principal <> 'S' ) ) as cid_cod_4 FROM pac , gih , cnv , emp emp_a , psv , cfg , cap , gcc , str , emp emp_b WHERE ( cap.cap_psv_assist = psv.psv_cod ) and ( cap.cap_cnv_cod = cnv.cnv_cod ) and ( cap.cap_pac_reg = pac.pac_reg ) and ( gih.gih_pac_reg = cap.cap_pac_reg ) and ( gih.gih_cap_num = cap.cap_num ) and ( emp_a.emp_cod = cap.cap_emp_cod_hosp ) and ( gcc.gcc_cod = cap.cap_gcc_cod ) and ( str.str_cod = gcc.gcc_str_cod ) and ( str.str_emp_cod = emp_b.emp_cod ) and ( ( cap.cap_pac_reg = 57849 ) and ( cap.cap_num = 1 ) and ( gih.gih_hsp_num_aut is null ) and ( gih.gih_num_prorrog is null ) No matter how hard I try I get the 'space for results table exhausted' error. I have a lot of free space (2Gb) in the dabase. I tried to create a lot of indexes, but there is no big table in this query. The largest table has 80.000 rows. I read in this article : http://lists.mysql.com/maxdb/12612 that the outer join above can be the problem. can you help me? Best regards, -- Daniel Castro - דניאל MSN: [EMAIL PROTECTED] Skype: danielti2005 ICQ : 316727989 Jabber: danielti Linux User: 387864 AOL User: Danielti2005 Sola Scriptura, Sola Gratia, Sola Fide, Solus Christus, Soli Deo Gloria
