Let’s say that you want to write a query to return a set of contact records and, for each of them, return the last appointment that you had with them.

This is actually a bit harder than it looks. The first part is easy enough, say something like this:


select company, contact from contact1
where
city='london'


You can link to history easily enough as well, like this:


select company, contact, ondate, resultcode from contact1, conthist
where
contact1.accountno=conthist.accountno and
city='london' and
srectype='a'
order by company, ondate desc


However, this has two problems. Firstly, it brings out every single appointment. Certainly we could limit it by date range, but what if the most recent appointment was before our cutoff? And, even then, we could still return multiple history lines instead of just the most recent one. Also, and this is a big problem, it won’t show us the contact record if there is no history at all.

What we need to do is to go back to our original description of what we wanted, and the clue is where I said ‘for each of them’. What we need is type of subquery which will take each contact record that the outer query returns and, armed with that information, go off to see what it can find in the conthist table. Luckily, SQL gives us such a thing with the APPLY operator.

We use it like this:


select company, contact, ondate, actvcode, resultcode, ref from contact1

outer apply
(select top 1 ondate, actvcode, resultcode, ref
from conthist
where rectype='a' and
accountno=contact1.accountno
order by ondate desc, ontime desc) as hist

where city='london'
order by ondate desc


The first line is selecting fields from both the contact1 and conthist tables but, instead of us doing a simple join as before, we’ve now inserted a subquery (the bit in brackets) that’s joined to the outer query by means of the ‘outer apply’ operator. The subquery is going to return the most recent appointment.

Why ‘outer’? That tells the outer query to return something even if the subquery comes back with nothing.

Why ‘apply’ and not just ‘join’? This is the magic part! APPLY allows the subquery to know something about the outer query. In this case, the subquery is able to see the accountno from contact1 in the outer query and match it to the accountno field that it can see locally – in other words, within conthist. Bingo!

Finally, notice that the subquery must be given an alias, or name, even if we don’t then refer to it explicitly. In this case I just called it ‘hist’.

So, the outer query can access the fields from the subquery (in order to display them and use them for sorting) and the subquery can access fields from the tables in the outer query (in order to do the job we’ve asked it to do).