Customised Query

SELECT

Z.PROJECT,

Z.SITE_ID,

Z.ZONE_CODE,

Z.DEPTH_FROM AS ZONE_DEPTH_FROM,

Z.DEPTH_TO AS ZONE_DEPTH_TO,

S.SAMPLE_ID,

S.DEPTH_FROM AS SAMPLE_DEPTH_FROM,

S.DEPTH_TO AS SAMPLE_DEPTH_TO,

(S.DEPTH_TO - S.DEPTH_FROM) AS SAMPLED_WIDTH,

F.FRACTION AS FRACTION,

F.SIZE_YIELD AS SIZE_YIELD

FROM

[DBO].[GB_NAMED_ZONE] AS Z

LEFT JOIN [DBO].[GB_SAMPLE] AS S

ON Z.PROJECT=S.PROJECT AND Z.SITE_ID=S.SITE_ID AND (Z.DEPTH_TO > S.DEPTH_FROM) AND (Z.DEPTH_FROM < S.DEPTH_TO)

LEFT JOIN [DBO].[GB_SAMPLE_FRACTION] AS F

ON S.PROJECT=F.PROJECT AND S.SITE_ID=F.SITE_ID AND S.SAMPLE_ID=F.SAMPLE_ID

WHERE

Z.PROJECT = '(Substitution parameter)'

AND Z.ZONE_CODE = '(Substitution parameter)'

AND Z.ZONE_TYPE = 'E'

ORDER BY

Z.PROJECT, Z.SITE_ID, Z.ZONE_CODE, S.DEPTH_FROM, S.DEPTH_TO, F.FRACTION