/* Advanced SQL
Inline Subquery
*/ 



/* Subquery to list plantings of crop/varieties.  */

select cropPlanting_ID, crop, variety, bay_bed
from tblcropPlanting join tblcropVariety
  on tblcropPlanting.cropVarID = tblCropVariety.cropVarID;
 
 
/* Subquery to list the amendments for crops if the
amendements are sulphur, potting mix, or mulch. */

select CropPlantingID, Amendment
from tblAmendment join tblCropPlantingAmend 
  on Amend_Code = AmendCode
where Amendment IN ('Sulphur', 'Potting Mix', 'Mulch')
order by Amendment;


/* get count of amendement/crop records per amendment */
select amendment, COUNT(*) as amend_count
from tblAmendment join tblCropPlantingAmend 
  on Amend_Code = AmendCode
group by amendment
order by COUNT(*);


/* Get a count of the harvests for each crop-planting. */

select CropPlantingID, COUNT(*) as hrvst_count
from tblCropHarvest
group by CropPlantingID;




/* In-line query using 3 subqueries in the FROM clause.
This query uses table aliases.  Note the column aliases
in the subquery become "regular" columns in the outer query. */

select Crp.cropPlanting_ID, crop, variety, hrvst_count, BB
from
  (select cropPlanting_ID, crop, variety, bay_bed as "BB"
   from tblcropPlanting join tblcropVariety
   on tblcropPlanting.cropVarID = tblCropVariety.cropVarID) Crp,
  (select CropPlantingID, Amendment
   from tblAmendment join tblCropPlantingAmend 
   on Amend_Code = AmendCode
   where Amendment IN ('Sulphur', 'Potting Mix', 'Mulch')) Amd,
  (select CropPlantingID, COUNT(*) as hrvst_count
   from tblCropHarvest
   group by CropPlantingID) Hrv
where amd.CropPlantingID = Crp.CropPlanting_ID
and Crp.CropPlanting_ID = hrv.CropPlantingID
order by hrvst_count desc;