CODEWITHSUNDEEP
mysqlsqlmysql-workbench
AznDevil92
AznDevil92

Reputation: 554

Query is executed but no data is displayed - MySQL

I am basically trying to pull a policy's synopsis that is under a certain institution that has a certain policy type and it must be the most recent policy.

I have written out a SQL query and when executing there is no error, however it is not pulling any data when there should be at least one data retrieved.

Below is my query:

select synopsis
from policy p
     left outer join institution_has_policy ihp
          on p.policy_id = ihp.policy_id
     left outer join institution i 
          on i.institution_id = ihp.institution_id   
where p.policy_type_id = 5  
      and i.institution_name = 'SSgA Funds Management, Inc.'
      and current_as_of_date = (select max(current_as_of_date) from policy p);

Is my sql statement correct?

Upvotes: 0

Views: 3459

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

This is your query:

select synopsis
from policy p left outer join
     institution_has_policy ihp
     on p.policy_id = ihp.policy_id left outer join
     institution i 
     on i.institution_id = ihp.institution_id   
where p.policy_type_id = 5 and
      i.institution_name = 'SSgA Funds Management, Inc.'
      current_as_of_date = (select max(current_as_of_date) from policy p);

No data is returned because the maximum as of date doesn't correspond to the policy and institution name. If you want only one row returned, then you can use order by and limit to get the latest:

select synopsis
from policy p left outer join
     institution_has_policy ihp
     on p.policy_id = ihp.policy_id left outer join
     institution i 
     on i.institution_id = ihp.institution_id   
where p.policy_type_id = 5 and
      i.institution_name = 'SSgA Funds Management, Inc.'
order by current_as_of_date desc
limit 1;

Upvotes: 2

poe123
poe123

Reputation: 1208

It is incorrect, as it's highly possible that the policy you selected won't be the actually most recent policy(of all, not of all the filtered ones). You can try deleting the date constraint, order it by date and select only the first row (LIMIT 1).

Upvotes: 1

Related Questions