CODEWITHSUNDEEP
mysqlsql
AznDevil92
AznDevil92

Reputation: 554

SQL Query is only selecting 2 out of 4 records - MySQL Workbench

Trying to select 4 fund names and their relating contacts. However, only two is being selected. I want to be able to select all 4 funds regardless if they have associated contact names.

Below is my SQL:

select mutual_Fund_name, concat(contact_first_name,' ',contact_last_name)  
from mutual_fund mf, contact c, mutual_fund_has_contact mfhc, institution i,  institution_has_mutual_Fund ihmf 
where mf.mutual_fund_id = mfhc.mutual_fund_id 
      and c.contact_id = mfhc.contact_id 
      and ihmf.mutual_fund_id = mf.mutual_fund_id 
      and i.institution_id = ihmf.institution_id 
      and mf.mutual_fund_name 
          in ('Vanguard Balanced Index Fund', 
              'Vanguard Consumer Discretionary Index Fund',
              'Vanguard Extended Market Index Fund', 
              'Vanguard Growth Index Fund') 
order by mutual_fund_name;

How do I get all funds with their contacts (regardless if the fund has any) to return in my sql statement?

Upvotes: 0

Views: 56

Answers (2)

Patrick Q
Patrick Q

Reputation: 6393

I'm not really sure why you would need explicit LEFT OUTER JOINs. This should be sufficient.

SELECT mutual_fund_name, CONCAT(contact_first_name,' ',contact_last_name)  
FROM mutual_fund mf
    LEFT JOIN mutual_fund_has_contact mfhc ON mf.mutual_fund_id = mfhc.mutual_fund_id
    LEFT JOIN contact c ON c.contact_id = mfhc.contact_id 
    LEFT JOIN institution i ON i.institution_id = ihmf.institution_id
    LEFT JOIN institution_has_mutual_Fund ihmf ON ihmf.mutual_fund_id = mf.mutual_fund_id
WHERE mf.mutual_fund_name IN ('Vanguard Balanced Index Fund', 
    'Vanguard Consumer Discretionary Index Fund',
    'Vanguard Extended Market Index Fund', 
    'Vanguard Growth Index Fund')
ORDER BY mutual_fund_name;

Upvotes: 1

Alvin Thompson
Alvin Thompson

Reputation: 5448

The abbreviated join syntax (which you're using) only represents an INNER JOIN, which excludes items if there is no corresponding row in the other table. You need to use LEFT JOIN (and the more descriptive join syntax) to include all mutual funds, even if they don't have a corresponding contact name.

select
  mutual_Fund_name,
  concat(contact_first_name,' ',contact_last_name) as 'contact name' 
from mutual_fund mf
left join mutual_fund_has_contact mfhc on mf.mutual_fund_id = mfhc.mutual_fund_id
left join contact c on mfhc.contact_id = c.contact_id
left join institution_has_mutual_Fund ihmf on mf.mutual_fund_id = ihmf.mutual_fund_id
left join institution i on ihmf.institution_id = i.institution_id
where mf.mutual_fund_name in (
  'Vanguard Balanced Index Fund', 
  'Vanguard Consumer Discretionary Index Fund',
  'Vanguard Extended Market Index Fund', 
  'Vanguard Growth Index Fund'
) 
order by mutual_fund_name;

Also, some of the joins you have are actually not necessary for this query, so you can get away with:

select
  mutual_Fund_name,
  concat(contact_first_name,' ',contact_last_name) as 'contact name' 
from mutual_fund mf
left join mutual_fund_has_contact mfhc on mf.mutual_fund_id = mfhc.mutual_fund_id
left join contact c on mfhc.contact_id = c.contact_id
where mf.mutual_fund_name in (
  'Vanguard Balanced Index Fund', 
  'Vanguard Consumer Discretionary Index Fund',
  'Vanguard Extended Market Index Fund', 
  'Vanguard Growth Index Fund'
) 
order by mutual_fund_name;

Upvotes: 1

Related Questions