mysql - How to write a sub query within another subquery? -

how go writing query for: each coral has @ least 2 samples during 1/1/2007 , 1/1/2008, list coral name , number of samples.

i think needs sub query have tried putting things around other ways , it's no working me. able 'spell out' me can understand going on , attempting similar queries this.

tables

reef [reefname, latitude, longitude, 2006_bleachedarea, summer_maximum_monthly_mean_temperature] key: reefname

reeftemp [reefname, dateofreading, temperaturereading] key: reefname, dateofreading

coral [coralcode, coralname, thermalthreshold] key: coralcode

coralsampling [sampleno, coralcode, reefname, dateofsampling, bleachpercent] key: sampleno

this have far. doubt correct @ all.

select coralcode, sampleno coralbleach__coralsampling   dateofsampling between '2007/1/1' , '2008/1/1' group sampleno 

you should able simple join, like;

select coral.name, count(coralsampling.sampleno) number_of_samples coral  join coralsampling    on coral.coralcode = coralsampling.coralcode  , coralsampling.dateofsampling between '2007/1/1' , '2008/1/1' group coral.name having count(coralsampling.sampleno) > 1 

this query corals, join them respective samples , group coral name (that means can use count count number of sample rows per coral name)
use having filter out corals don't have 2 samples or more.

joins bit complex describe in detail in short answer format, suggest read on join , group able construct queries these easily.