Retrieve columns in a multiple SQL subquery

Question

I have a straightforward table hierarchy involving 4 tables;

slices -> blocks -> ribbons -> glassplate

Some logic about the above semantics:

A "slice" is a slice of (biological) tissue. From one slice, multiple "blocks" are created. The blocks table has a slice_id foreign key.
From one block, multiple "ribbons" are constructed. Ribbons table have a block_id foreign key.
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.

Adam · Accepted Answer · 2018-03-16 18:49:40Z

1

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;

answered Mar 16, 2018 at 18:49

Adam

5,6296 gold badges35 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Totte Karlsson Over a year ago

Worked perfectly! Will check out JOIN logic.

Collectives™ on Stack Overflow

Retrieve columns in a multiple SQL subquery

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related