Using a Select in a Where clause for another Select

Question

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.

e.g. :

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.

Answer 1

Is this what you're aiming for? Make sure the fields that are being compared are comparable (i.e. both fields are numeric, text, boolean, etc).

SELECT * FROM Individual
INNER JOIN Publisher
ON Individual.IndividualId = Publisher.IndividualId
WHERE Individual.IndividualId = (SELECT someID FROM table WHERE blahblahblah)

If you wish to select based on multiple values:

SELECT * FROM Individual
INNER JOIN Publisher
ON Individual.IndividualId = Publisher.IndividualId
WHERE Individual.IndividualId IN (SELECT someID FROM table WHERE blahblahblah)

Answer 2

You can just rewrite that as another JOIN. This is normally simplest and fastest:

SELECT i.*, p.*
FROM   individual    i
JOIN   publisher     p USING (individualid)
JOIN   another_table a ON a.identifier = i.individualid
WHERE  a.something = 'something'

I also simplified somewhat and did away with the gratuitous CamelCase spelling of identifiers.