SELECT CASE / JOIN when no line

Sorry this one is a bit of a headache. I'll start with the example:

Tables:

TownCountry

Record |  Town  | CountryCode
-------+--------+-------------
1      | London | A1
2      | Cardiff| A2
3      | Hull   | A1
4      | Luton  | A1

ReFData

Type    |  Code   | Country
--------+---------+-------------
Country | A1      | England
Country | A2      | Wales

If my query is:

select a.Town, b.Country from TownCountry a, RefData b, TownCountry c
where a.Record=1
and b.Code=c.CountryCode and c.Record=2

I get:

London | Wales

However, if I change the code for Wales to A3, and keep the query the same, by result returns no rows.

What I want, in the example where Wales is A3, is for my result to be:

London | (empty)

I've tried COALESCE:

select a.Town, COALESCE(b.Country,'empty') from TownCountry a, RefData b, TownCountry c
where a.Record=1
and b.Code=c.CountryCode and c.Record=2

but this returned no rows

I also tried select case, right and left joins, but still no rows.

Here's a simpler example that my good friend just gave me while discussing:

Towns

Record |  Town
-------+--------
1      | London
2      | Cardiff
4      | Luton

select a.Town, b.Town, c.town, d.Town
from Towns a, Towns b, Towns c, Towns d
where a.Reocrd=1 and b.Reocrd=2 and c.Reocrd=3 and a.Reocrd=4

I want to return

a.Town | b.Town | c.Town | d.Town
-------+--------+--------+--------
London | Cardiff| NULL   | Luton

Any help much appreciated.

---

The problem here is that the Translation table does not have entry for blank raw values. As a result, there is nothing in the Translation table that matches so no rows are returned.

This particular problem can be solved by adding a row to the Translation table, or more precisely, using union to add the row:

select a.Town, b.Country from TownCountry a,
(select Code, Country from RefData b
union select '' as Code, 'Not found' as Country from RefData c), TownCountry c
where a.Record=1
and b.Code=c.CountryCode and c.Record=2

SQL Love, Wing