CODEWITHSUNDEEP
postgresqlsubquery
Totte Karlsson
Totte Karlsson

Reputation: 1341

Retrieve columns in a multiple SQL subquery

I have a straightforward table hierarchy involving 4 tables; 

slices -> blocks -> ribbons -> glassplate

Some logic about the above semantics:

  1. A "slice" is a slice of (biological) tissue. From one slice, multiple "blocks" are created. The blocks table has a slice_id foreign key.
  2. From one block, multiple "ribbons" are constructed. Ribbons table have a block_id foreign key.
  3. Ribbons are placed on a barcoded glassplate (glassplate_id). (Typically just one ribbon, but possibly more). Ribbons table have a glassplate_id foreign key.

I'm trying to construct a (postgres) query that will retrieve relevant information about ribbon(s), block and slice, using the glassplates barcode, i.e. glassplate_id.

My current query, using glassplate ID = '163'

SELECT * from slices WHERE slices.id IN 
(
    SELECT blocks.slice_id FROM blocks WHERE blocks.id IN 
    ( 
        SELECT block_id FROM ribbons WHERE glassplate_id = 163
    )
)

do return the information I need from the slices table.

Question is; how to retrieve relevant fields in the blocks and ribbons table as well, in the same query? Relevant columns in those tables would be the ones that I choose.

Upvotes: 0

Views: 31

Answers (1)

Adam
Adam

Reputation: 5599

Read about JOINS.

SELECT
    *
FROM
    slices
    JOIN blocks
    ON (slices.id = blocks.slice_id)
    JOIN ribbons
    ON (blocks.id = ribbons.block_id)
WHERE
    ribbons.glassplate_id = 163;

Upvotes: 1

Related Questions