PostgreSQL Problem with Conditional Statement Returning Zero Results

Question

My goal is to return depths from data_table1 grouped by id_number and between the two depth thresholds in data_table2. This should typically return a number of depths per well when a well has both the upper and lower thresholds in data_table2.

data_table1 has geological data from a number of different wells arranged by depth. Each row represents one depth in the table. Each well has a unique id_number in both data_table1 and data_table2. data_table2 has three columns: id_number, formation and depth_md. The threshold category information is stored in the formation column; each threshold occurs at a specific depth for each well.

The query currently returns no results. If I replace the 2nd AND in the WHERE statement with an OR, I get more results than is possible (double counting).

SELECT data_table1.id_number, COUNT(data_table1."depth") FROM "data_table1"
JOIN data_table2 ON data_table2.id_number = data_table1.id_number
WHERE (data_table2.formation ILIKE 'lower_threshold' AND data_table1."depth" < data_table2.depth_md) AND (data_table2.formation ILIKE 'upper_threshold' AND data_table1."depth" > data_table2.depth_md)
GROUP BY data_table1.id_number

You should post your data model (create table's queries), some example data (insert queries) and the desire result; so we can execute your query. You could use something like dbfiddle. — nachospiu
– nachospiu, Commented Apr 17, 2021 at 13:38

Don R · Accepted Answer · 2021-04-06 16:00:39Z

1

It looks like values of lower_threshold and upper_threshold are never in the same row, right? One way to approach this is to join data_table2 twice, once for the upper bound and once for the lower:

SELECT
   data_table1.id_number
   ,COUNT(data_table1.depth)
FROM
    data_table1
    INNER JOIN data_table2 AS t2l ON
        t2l.id_number = data_table1.id_number
        AND t2l.formation ILIKE 'lower_threshold'
    INNER JOIN data_table2 AS t2u ON
        t2u.id_number = data_table1.id_number
        AND t2u.formation ILIKE 'upper_threshold'
WHERE
    data_table1.depth < t2l.depth_md
    AND data_table1.depth > t2u.depth_md
GROUP BY data_table1.id_number

edited Apr 6, 2021 at 16:00

answered Apr 5, 2021 at 21:56

Don R

6395 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL Problem with Conditional Statement Returning Zero Results

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related