[SQL] Big Sub-select statement

[Bernie Huang]

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

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:[EMAIL PROTECTED]
title:Programmer
x-mozilla-cpt:;0
fn:Bernie Huang
end:vcard