Received: from rdms.pyr.ec.gc.ca (fwvan1.pyr.ec.gc.ca [199.212.20.2]) by hub.org (8.10.1/8.11.0) with ESMTP id e9HIxeW89610 for ; Tue, 17 Oct 2000 14:59:40 -0400 (EDT) (envelope-from bernie.huang@ec.gc.ca) Received: from ec.gc.ca (IDENT:huangb@rdms.pyr.ec.gc.ca [131.235.150.90]) by rdms.pyr.ec.gc.ca (8.9.3/8.9.3) with ESMTP id LAA02509 for ; Tue, 17 Oct 2000 11:53:47 -0700 Message-ID: <39ECA036.EFF68763@ec.gc.ca> Date: Tue, 17 Oct 2000 11:53:42 -0700 From: Bernie Huang Organization: Environment Canada X-Mailer: Mozilla 4.72 [en] (X11; U; Linux 2.2.14-5.0 i686) X-Accept-Language: en MIME-Version: 1.0 To: PGSQL-SQL Subject: Big Sub-select statement Content-Type: multipart/mixed; boundary="------------5FFA04C36C3E7F20B117F9CF" X-Archive-Number: 200010/157 X-Sequence-Number: 1819 This is a multi-part message in MIME format. --------------5FFA04C36C3E7F20B117F9CF Content-Type: text/plain; charset=iso-8859-15 Content-Transfer-Encoding: 7bit Hi, Let's say if I have a log table with (log_id, emp_id, book_id, author_id), and there are foreign tables related to each id except the log_id. Is it possible to write up a big sub-select statement to retrieve all possible info? eg1; (big sub_select) select ltb.log_id, ltb.emp_id, ltb.book_id, ltb.author_id from log_tb ltb where ltb.emp_id in (select etb.name etb.phone etb.email from emp_tb etb where etb.emp_id=ltb.emp_id) and ltb.book_id in (select btb.name ... ) and ... [same for author_id]; So, is it possible and efficient to retrieve all info in one big statement? or is there other better ways to do it? Right now, here's what I do, select log_id, emp_id, ... from log_tb; while(not end of rows) { select name, phone, email from emp_tb where emp_id=log_tb.emp_id; ... [same for book_tb and author_tb] } I am thinking if it's in one big statement, then I don't need to look around in the script to change the select statements. Thank you. =) - Bernie --------------5FFA04C36C3E7F20B117F9CF Content-Type: text/x-vcard; charset=iso-8859-15; name="bernie.huang.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Bernie Huang Content-Disposition: attachment; filename="bernie.huang.vcf" begin:vcard n:Huang;Bernie tel;fax:(604)664-9195 tel;work:(604)664-9172 x-mozilla-html:TRUE org:Environment Canada;Standards and Technology Services adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada version:2.1 email;internet:bernie.huang@ec.gc.ca title:Programmer x-mozilla-cpt:;0 fn:Bernie Huang end:vcard --------------5FFA04C36C3E7F20B117F9CF--