CODEWITHSUNDEEP
sqldatabasepostgresql
electricjelly
electricjelly

Reputation: 436

Postgresql excluding results based on subquery

I have a postgresql query that looks like this:

SELECT cats.cat_name,
    cats.breed,
    cats.color,
    cats.gender,
    cats.shelter_location,
    microchip_num.id as microchip,
    release_num.release_num AS release_num,
    cat_files.file,
    max(adoption_num.adoption_num),
        CASE
            WHEN release_num.release_num ~~ 'R%'::text THEN '2'::text
            ELSE '1'::text
        END AS adoptability,
        CASE
            WHEN cats_age.age < '12'::bigint THEN '1'::text
            WHEN cats_age.age > '11'::bigint THEN '2'::text
            ELSE NULL::text
        END AS cat_age
   FROM cats
     JOIN microchip_num ON cats.fk_microchip = microchip_num.id
     JOIN release_num ON release_num.fk_cat_micro = cats.fk_microchip
     LEFT JOIN adoption_num ON adoption_num.fk_microchip_cats = cats.fk_microchip
     LEFT JOIN cats_deceased ON cats.fk_microchip = cats_deceased.cats_micro
     LEFT JOIN cats_returned ON adoption_num.id = cats_returned.adoption_num
     LEFT JOIN cat_files ON cats.fk_microchip = cat_files.fk_cats_key
     LEFT JOIN cats_age ON cats_age.fk_cats_micro = cats.fk_microchip
  WHERE cats.cat_name IS NOT NULL AND microchip_num.microchip !~~ 'null%'::text AND '1'::text =
        case
            WHEN (adoption_num.adoption_num IN ( 
                    select adoption_num.adoption_num 
                        from cats
                            join adoption_num on cats.fk_microchip = adoption_num.fk_microchip_cats 
                except
                    select adoption_num.adoption_num 
                        from cats
                            join adoption_num on cats.fk_microchip = adoption_num.fk_microchip_cats 
                            join cats_returned on adoption_num.id = cats_returned.adoption_num)) 
                THEN '0'::text
            ELSE '1'::text
        END AND cats.shelter_location = '1'::bigint AND cats_deceased.deceased = false
  GROUP BY cats.cat_name, cats.breed, cats.color, cats.gender, cats.shelter_location, microchip_num.microchip, cat_files.file, release_num.release_num, cats_age.age, microchip_num.id, adoption_num.adoption_num;

The problem I am having is with this section here:

case
            WHEN (adoption_num.adoption_num IN ( 
                    select adoption_num.adoption_num 
                        from cats
                            join adoption_num on cats.fk_microchip = adoption_num.fk_microchip_cats 
                except
                    select adoption_num.adoption_num 
                        from cats
                            join adoption_num on cats.fk_microchip = adoption_num.fk_microchip_cats 
                            join cats_returned on adoption_num.id = cats_returned.adoption_num)) 
                THEN '0'::text
            ELSE '1'::text
        END

To put this in simple terms I have table FOO, BAR, and BAZ. table FOO contains all available values, table BAR is a child of FOO and BAZ is a child table of BAR

a graphical demonstration:

FOO
 |
 |
 -----BAR
 |     |
 |     |
 -----------BAZ

How can I tell postgresql to pull all the values from FOO where BAR values are NULL but also where BAZ values are included, only if there are no additional values that appear in FOO that do not appear in BAZ

example:

table **FOO**
cars_id   car_type   
   1       Toyota
   2       Ford
   3       Sega
table **BAR**
  id   sold    car_id_foreign_key (id of row from the *Foo* table) date_of_sale
  1     Yes           1                                              1/1/20
  2     yes           2                                              1/2/20
  3     yes           1                                              3/3/20
table **BAZ**  
   Returned    date_returned sale_id_foreign_key (id of row from the *BAR* table*)
      yes           2/2/20         1

In this example all I would want to show in my query would be the Sega car since the toyota car was sold and returned but sold again and the Ford car was sold and never returned

Upvotes: 0

Views: 88

Answers (1)

COY
COY

Reputation: 704

Sorry for being lazy but I am only willing to provide the key idea here... Include something like this in WHERE of (select FOO):

and not exists (
    select 0 
    from
        BAR
        left join BAZ on BAZ.fk_for_bar = BAR.pk
    where
        BAR.fk_for_foo = FOO.pk
        and BAZ.fk_for_bar is null
)

The logic used is what I can directly read from your example.
What you need is, the car must not be in sold status.
What a "sold status" means? It means you have an invoice that sells it, but never have another invoice that recollects it.
The inner query is selecting BAR that does not match any BAZ.

Upvotes: 1

Related Questions