0

I have two tables

Table1 :

efd_details( id ) 

Table2 :

efd_detail_status( detail_id  , status )

Now the data as following :

select * from efd_details;

Output :

ID
1
2
3
4
5
select * from efd_detail_status ;

Output :

DETAIL_ID | STATUS
1         |   's'
2         |   's'
3         |   's'
4         |   null
5         |   'f'

What I need to do is get all the details (efd_details) ONLY if ALL the statuses in efd_detail_status = 's'. In the example above, I would retrieve ZERO records since two of the status are != 's'. If ALL the status = 's' then return ALL rows from cms_efd.

I have tried variations of EXISTS and the following:

select e.*
  from cms.cms_efd e
where exists(select 1 
               from cms.cms_efd_status s 
              where s.detail_record_id = e.detail_record_id 
                and IFNULL(s.contrib_calc_status,'f') != 's'
             having count(e.detail_record_id)=count(s.detail_record_id))
    and e.earnings_file_id = 400676;

cms_efd cms_efd_status Appreciate the help. -Branden

4
  • If no other statuses then SUM(CASE WHEN status = 's' THEN 0 ELSE 1 END) = 0. For to ensure that there is no details without status use LEFT JOIN. – Akina Apr 13 at 4:07
  • WHERE NOT EXISTS ( SELECT 1 ... WHERE status != ... ) – Rick James Apr 13 at 6:13
  • @RickJames, sorry, that doesn't work. In the images in my comments, 400676 has all statuses that = 's', however, your query returns zero results: ``` select e.* from cms.cms_efd e WHERE NOT EXISTS ( SELECT 1 from cms.cms_efd_status s WHERE IFNULL(s.contrib_calc_status,'f') != 's' ) and e.earnings_file_id = 400676;``` – user3874930 Apr 13 at 15:07
  • @RickJames, sorry mate, I left out a filter. This looks promising. select e.* from cms.cms_efd e WHERE NOT EXISTS ( SELECT 1 from cms.cms_efd_status s WHERE s.earnings_file_id=e.earnings_file_id and IFNULL(s.contrib_calc_status,'f') != 's' ) and e.earnings_file_id = 400290; – user3874930 Apr 13 at 15:13
0

Turn it around and do something like

WHERE NOT EXISTS ( SELECT 1 FROM cms.cms_efd_status
                   WHERE ... != ... )
  AND e.earnings_file_id = 400676

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.