I have made a draft remote application on the top of libpq for PostrgreSQL. It behaves well, but I have profiled the general functionning of the application. For each final business result that I produce, it happens that I call something like 40 select clause (over tcpip).
I have reminiscences from SQL-Server reminding me to minimize the number of interactions between my remote application and the database. Having analyzed my selects, I do think I could reduce this number to 3 select clause, using jointures. But I don't remember the syntax for using the result of a select in another select.
SELECT * FROM individual INNER JOIN publisher ON individual.individual_id = publisher.individual_id WHERE individual.individual_id = 'here I would like to use the results of a another select'
this other select would be simply of the kind:
SELECT identifier FROM another_table WHERE something='something'
How would you write that ? What is the name for describing the action of redirecting one select into another , if any name ?
EDIT: in the answer given i've been ask to give the tables layout. Here it is simplified, declined a number of times for different item_types... ( 3 totally different types, hence the 3 sql queries if optimized).
table passage PK: id_passage text business_field_passage table item PK: id_item FK: id_passage text business_field_item table item_detail PK: id_item_detail FK: id_item text business_field_item_detail bytea image_content
there are several id_item for one id_passage. there are several id_item_detail for one id_item.