Reputation: 436
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
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