How to find a non-existing value from another table in mysql using ONE single QUERY

I want to find number of types of produce of each farmer, names of farmer and the total number of produces but I am getting stuck in the part trying to display farmers who do not produce anything in the supplies and then in another query I want to extend it to display farmers with highest produces but without using the limit function.

    -- Table Farmer
create table Farmer(
    FarmerID char(3),
    FarmerName varchar(40),
    FarmName varchar(40),
    FarmLocation varchar(40),
    primary key(FarmerID));


-- Table ProduceType
create table ProduceType(
    ProdTypeID char(3),
    ProdTypeName varchar(20),
    primary key(ProdTypeID));

-- Table Produce
create table Produce(
    ProduceID char(3),
    ProduceName varchar(20),
    ProdTypeID char(3),
    primary key(ProduceID),
    foreign key(ProdTypeID) references ProduceType(ProdTypeID));
-- Table Supplies
create table Supplies(
    ProduceID char(3),
    FarmerID char(3),
    Weight decimal(5,2),
    Metric varchar(10),
    PricePerWeight decimal(5,2),
    primary key(ProduceID,FarmerID),
    foreign key(ProduceID) references Produce(ProduceID),
    foreign key(FarmerID) references Farmer(FarmerID));

My current attempt which is not working

    select count(p.prodtypeid) as 'number of types of produce', farmername, count(p.produceid) as 'total number of produces'
from farmer f, supplies s, producetype pt, produce p
where f.farmerid = s.farmerid
and p.prodtypeid = pt.prodtypeid
and s.produceid = p.produceid
or not exists ( select ff.farmerid
                from farmer ff, supplies ss where ff.farmerid = ss.farmerid)
group by farmername;

The output should be

Farmername | number of produce | number of producetye
Daniel          | 2                 | 2
Alex           |2                  |2
Lewis          | 1                  |1
Michale         | 0                 | 0

Answer

SELECT COUNT(DISTINCT p.ProdTypeID) AS `number of types of produce`, 
       f.FarmerName, 
       COUNT(p.ProduceID) AS `total number of produces`
FROM Farmer f
JOIN Supplies s USING (FarmerID)
JOIN Produce p USING (ProduceID)
GROUP BY FarmerName;

This query gives “number of types of produce of each farmer, names of farmer and the total number of produces”.

SELECT FarmerName
FROM Farmer
WHERE NOT EXISTS ( SELECT NULL
                   FROM Supplies
                   WHERE Farmer.FarmerID = Supplies.FarmerID  );

This query gives “farmers who do not produce anything in the supplies”.

The restriction – the lost relations (the rows in Supplies where FarmerId has definite value but ProduceID is NULL) not exists. For to guarantee this define both mentioned columns as NOT NULL in table structure.

---

The output should be ..

SELECT f.FarmerName, 
       COALESCE(COUNT(p.ProduceID), 0) AS `total number of produces`,
       COALESCE(COUNT(DISTINCT p.ProdTypeID), 0) AS `number of types of produce`
FROM Farmer f
LEFT JOIN Supplies s USING (FarmerID)
LEFT JOIN Produce p USING (ProduceID)
GROUP BY FarmerName;

Left joining does not allow to reject the farmer rows which has no according rows in another tables. COALESCE function replaces NULLs obtained for such rows with zero value.