CODEWITHSUNDEEP
mysql
Blake
Blake

Reputation: 151

Trying to join the results of two selects in sql

I am new to mysql and can not figure out how to join the results from these two selects. Any suggestions?

select COUNT(COLORANTCODE) as Colorant_Count from Formula group by COLORANTCODE;

select COLORANTNAME from Colorant;

Nothing i have tried has worked.

Formula contains COLORANTCODE which is a FK to Colorant that contains COLORANTCODE AND COLORANTNAME.

I need the resulting table to contain the COLORANTNAME and the count of how many times it appears in Formula.

Upvotes: 0

Views: 42

Answers (2)

peterm
peterm

Reputation: 92785

Make use of LEFT JOIN

SELECT c.colorantname, COALESCE(COUNT(f.colorantcode), 0) count
  FROM colorant c LEFT JOIN Formula f
    ON c.colorantcode = f.colorantcode
 GROUP BY c.colorantcode, c.colorantname

Recommended reading:

Upvotes: 1

Vorpal Swordsman
Vorpal Swordsman

Reputation: 375

Select COLORANTCODE, Count(*) as Colorant_count  
from Formula  
Group by COLORANTCODE

Upvotes: 0

Related Questions