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 |
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 |