Alfasatwi Alfasatwi - 5 months ago 7
MySQL Question

(Needs Optimizing) - MySQL Select from several tables with the latest value in one table

I have the following tables in my database


  • Beneficiary.
    PK: BeneficiaryId

    this table lists the beneficiaries from a health insurance database.

  • PolicyBeneficiary.
    PK: BeneficiaryId AND PolicyId
    This is the resulting table from a Many to Many relationship between the beneficiary and an insurance policy.

  • Policy.
    PK: PolicyId
    This table lists all the policies, each with a beginning date and end date of the policy.

  • TableOfBenefits.
    PK: PolicyId AND BenefitId
    This table is the resulting table from a Many to Many relationship between the policy and the benefit, it contains the limit, maximum number of sessions for each benefit...etc for that policy.

  • BenefitId.
    PK: BenefitId
    This table lists the benefits along with their categories.



What I am trying to do here is get the rows from
TableOfBenefits
table for a one
Beneficiary
, based on the last policy he is listed in in the
PolicyBeneficiary
table.

I managed to come up with this joined sub-query to get the last policy for a beneficiary, then join it with the data I need, it gets the job done, but I am not sure that this is the optimized solution for my problem.

SELECT
Benefit.BenefitId
,Benefit.Name
,TableOfBenefits.BenefitLimit
,TableOfBenefits.Percentage
,TableOfBenefits.Sessions
FROM TableOfBenefits
INNER JOIN Benefit
ON TableOfBenefits.BenefitId = Benefit.BenefitId
INNER JOIN (
SELECT Policy.PolicyId
FROM Policy
INNER JOIN PolicyBeneficiary
ON Policy.PolicyId = PolicyBeneficiary.PolicyId
WHERE PolicyBeneficiary.BeneficiaryId = 2
ORDER BY PolicyId DESC LIMIT 1
) Policy
ON TableOfBenefits.PolicyId = Policy.PolicyId


I am trying to find out whether this is the best solution for my problem or if there is another more optimized solution.

Answer

Added small modification removed where clause and it added to the join operation in that case initially it will filter that result and then join it with other table

SELECT 
     Benefit.BenefitId
    ,Benefit.Name
    ,TableOfBenefits.BenefitLimit
    ,TableOfBenefits.Percentage
    ,TableOfBenefits.Sessions 
FROM TableOfBenefits 
INNER JOIN Benefit 
    ON TableOfBenefits.BenefitId = Benefit.BenefitId 
INNER JOIN (    
            SELECT Policy.PolicyId 
            FROM Policy 
            INNER JOIN PolicyBeneficiary 
                ON Policy.PolicyId = PolicyBeneficiary.PolicyId     
            AND PolicyBeneficiary.BeneficiaryId = 2
            ORDER BY PolicyId DESC LIMIT 1 
            ) Policy 
    ON TableOfBenefits.PolicyId = Policy.PolicyId
Comments