sql - Where clause affecting two different select statements -
i need count payers , mortals separately have zip code of 44444. zip codes stored in same tables ids on 2 separate tables in many-to-many relationships. have written this:
select count(mortal_zip) "mortals in 44444" ,count(payer_zip) "payers in 44444" (select am.zip_code mortal_zip address join mortal_address on mortal_address.address_id = am.address_id join mortal on mortal.mortal_id = mortal_address.mortal_id trim(am.zip_code) = '44444' ) m, (select ap.zip_code payer_zip address ap join payer_address on payer_address.address_id = ap.address_id join payer on payer.payer_id = payer_address.payer_id trim(ap.zip_code) = '44444' ) p;
i know this: there no payers zip code of 44444 there 3 mortals zip code of 44444. reason getting there 0 mortals zip code of 44444. if ask mortals in 44444, needed. if ask count of mortals , count of payers 444444 0 on both sides.
additionally, have tried re writing whole thing sub selects.
select count(m.mortal_zip) "mortals in 44444" ,count(p.payer_zip) "payers in 44444" (select am.zip_code mortal_zip address trim(am.zip_code) = '44444' , am.address_id in (select mortal_address.address_id mortal_address mortal_address.mortal_id in (select mortal.mortal_id mortal ) ) ) m, (select ap.zip_code payer_zip address ap trim(ap.zip_code) = '44444' , ap.address_id in (select payer_address.address_id payer_address payer_address.payer_id in (select payer.payer_id payer ) ) ) p;
i same result.
why function in different select statement affecting other select statement?
update
i've re written query these 2 queries returning different values.
this:
select m.mortal_zip "mortals in 44444" ,p.payer_zip "payers in 44444" (select count(am.zip_code) mortal_zip address trim(am.zip_code) = '44444' , am.address_id in (select mortal_address.address_id mortal_address mortal_address.mortal_id in (select mortal.mortal_id mortal ) ) ) m, (select count(ap.zip_code) payer_zip address ap trim(ap.zip_code) = '44444' , ap.address_id in (select payer_address.address_id payer_address payer_address.payer_id in (select payer.payer_id payer ) ) ) p;
returns:
mortals in 44444 payers in 44444 ---------------- --------------- 3 0
this:
select mortal_zip "mortals in 44444" ,payer_zip "payers in 44444" (select count(am.zip_code) mortal_zip address join mortal_address on mortal_address.address_id = am.address_id join mortal on mortal.mortal_id = mortal_address.mortal_id trim(am.zip_code) = '44444' ) m, (select count(ap.zip_code) payer_zip address ap join payer_address on payer_address.address_id = ap.address_id join payer on payer.payer_id = payer_address.payer_id trim(ap.zip_code) = '44444' ) p;
returns:
mortals in 44444 payers in 44444 ---------------- --------------- 5 0
your query trying join
empty result set result set has 3 rows. of course, returns empty result set. instead, run subselects count
s themselves, or alternatively:
select count(ma.address_id) "mortals in 44444", count(pa.address_id) "payers in 44444" address left outer join mortal_address ma on ma.address_id = a.address_id left outer join payer_address pa on pa.address_id = a.address_id a.zip_code = '44444'
Comments
Post a Comment