select
t1.tid,
t1.alias as alias,
t1.asset_label as asset_label,
t1.terminal_id as terminal_id,
t1.location_address as terminal_address,
t1.last_updated as last_connection,
t1.name as questionnaire_name,
t1.questionnaire_id as questionnaire_id,
t1.state as state,
t1.value as publishing_state,
response_stats.rcount as rcount,
response_stats.last_response as last_response
from
(select
t.id as tid,
t.alias,
t.asset_label,
t.terminal_id,
t.location_address,
t.last_updated,
qnr.name,
qnr.id as questionnaire_id,
qnr.status as state,
at.value
from terminal t
left outer join
(delivery_stats d,
terminal_activity ta,
questionnaire qnr,
activity_type at)
on
d.terminal_id = t.id
and d.terminal_activity_id = ta.id
and ta.questionnaire_id = qnr.id
and ta.activity_type = at.id
and qnr.status = "L"
where
t.id in(1, 20, 21, 22, 23)) as t1
left outer join
(select
tcq.terminal_id tid,
tcq.questionnaire_id qid,
count(*) rcount,
max(uploaded_date) as last_response
from
tcquestionnaire tcq
group by
tcq.terminal_id, tcq.questionnaire_id) as response_stats
on
t1.tid = response_stats.tid
and t1.questionnaire_id = response_stats.qid;
Whilst technically correct - I'm not entirely satisfied of the need for the the top level left outer join which creates a full derived table rather than having a correlated sub-query / nested join that contains only the rows actually relevant to the result set. Something to think about perhaps, on a rainy (very rainy!?!) day :)
.
Posts
