How should this SQL query be constructed? (Postgres)

Question

I've never had to check for something like this before, so I'm hoping y'all can help. I'm not sure if it's best to create a temporary table, then check if an item is in it or if there's a better way.

Each row in the table represent an object that may have a parent. The parent is stored in the same table. In this example, the parent_id field holds the primary key of a row's parent. I'm trying to select all rows in the table which have the type column set to a particular value and whose parent rows have a 'z' in the field_b column. The part in parenthesis obviously needs work...

SELECT s_id, s_text, s_parent_id
FROM sections 
WHERE s_derivedtype >= 10000 AND

if this returns anything

SELECT s_id
FROM sections
WHERE s_id = {the s_parent_id from the first query) AND s_flags LIKE '%z%'

I've updated this to hopefully be easier to read...

What's the most efficient way to do this? I'm expecting something like 100k rows returned out of 18m in the table, so decent performance is non-trivial.

Provide the schema and small set of example data: "the parent_id field holds the primary key of a row's parent" sounds confusing. Is this some form of EAV or is it just explained awkwardly? — user166390
– user166390, Commented Feb 16, 2013 at 23:08
(I believe you just want a self-join like FROM table1 AS c JOIN table1 AS p ON c.parent_id = p.id, but I'm not in a guessing mood today.) — user166390
– user166390, Commented Feb 16, 2013 at 23:10
That's probably on the right track. I haven't posted the complete schema, but have hopefully made this a bit clearer as to what I'm asking. — thatthatisis
– thatthatisis, Commented Feb 16, 2013 at 23:18
Is there only a single immediate parent -- 1 child to 1 parent -- or are you looking for a recursive option? — sgeddes
– sgeddes, Commented Feb 16, 2013 at 23:20
Non-recursive. Thanks for the quick help everyone! Joining the table with itself did the trick. I've done many joins on tables in the past, but it unfortunately didn't occur to me to join a table with itself. — thatthatisis
– thatthatisis, Commented Feb 16, 2013 at 23:23

Clodoaldo Neto · Accepted Answer · 2013-02-16 23:16:52Z

2

SELECT key_field, field_a 
FROM
    t s
    inner join
    t p on p.key_field = s.key_field
WHERE
    s.type = 1
    AND p.field_b LIKE '%z%'

answered Feb 16, 2013 at 23:16

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

Comments

T I · Accepted Answer · 2013-02-16 23:16:28Z

1

try

SELECT t1.key_field, t1.field_a 
FROM tbl AS t1 
WHERE t1.type = 1 AND parent_id = (SELECT t2.id FROM tbl AS t2 
                                    WHERE t2.id = t1.parent_id
                                      AND t2.field_b LIKE '%z%')

or

SELECT t1.key_field, t1.field_a
FROM tbl AS t1 
INNER JOIN tbl AS t2 ON t2.id = t1.parent_id
WHERE t1.type = 1
AND t2.field_b LIKE '%z%'

answered Feb 16, 2013 at 23:16

T I

9,9634 gold badges31 silver badges51 bronze badges

1 Comment

thatthatisis Over a year ago

It turned out to be a decent case for the subquery. Thanks TI.

Collectives™ on Stack Overflow

How should this SQL query be constructed? (Postgres)

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related