Big Sub-select statement

From: Bernie Huang <bernie(dot)huang(at)ec(dot)gc(dot)ca>
To: PGSQL-SQL <pgsql-sql(at)postgresql(dot)org>
Subject: Big Sub-select statement
Date: 2000-10-17 18:53:42
Message-ID: 39ECA036.EFF68763@ec.gc.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Attachment Content-Type Size
bernie.huang.vcf text/x-vcard 315 bytes

Browse pgsql-sql by date

  From Date Subject
Next Message hubert depesz lubaczewski 2000-10-17 19:02:51 problem with select where like ']'
Previous Message Bernie Huang 2000-10-17 18:02:19 nested transactions