SELECT CASE/JOIN when no rows

Question

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.

First, TownCount isn't a table, and your friend's code can't possibly run. Copy and paste is more reliable than typing. Second, what you want doesn't make sense to me. Can you explain the problem you're trying to solve, rather than describing how you're trying to solve it? The only sane data I can imagine returning with London is either Hull (for towns) or England (for countries). — Mike Sherrill 'Cat Recall', Jul 16 '11 at 22:30
Why are you joining on TownCountry twice? You should only need it once. — eaolson, Jul 16 '11 at 22:54

score 0 · Answer 1 · answered Jul 16 '11 at 22:14

If you want to keep rows even where there is no match on the column you're joining on, you need to do an OUTER JOIN rather than an INNER JOIN.

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.apdv.porting.doc/doc/r0052878.html

Bohemian · Answer 2 · 2011-07-17T21:36:34.067

0

You're not really doing joins, and you need an outer join (ie LEFT JOIN).

What you want is something like this:

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

EDITED: I put "and c.Record=2" into the join clause. This little trick is a good one - it retains the condition, but doesn't require a joined row

edited Jul 17 '11 at 21:36

answered Jul 16 '11 at 23:05

Bohemian

412,405
93
575
722

Why aren't I really doing joins? And Why do you have 2 Left Joins? Essentially, what I'm trying to do is turn a 2D array into 1 row. The problem being that some of the data needs to be transformed (CountryCode). If we are left joining RefData (i.e. returning everything from RefData where there's no match in CountryCode) then what will be returned from RefData if there is no match between RefData and CountryCode? Well the way I see it is, we are using a 2nd instance of CountryCode as a lookup into RefData, so if there is no match, what could possibly be returned? – Paul Jul 17 '11 at 15:49
Can we use the 2nd example I mentioned as an example. In the third instance of the table (Towns c) there is no match, so the whole query returns no rows. What I need to do is return a Null or Empty field. – Paul Jul 17 '11 at 15:50
There is a way - you put the condition into the join. See updated answer – Bohemian Jul 17 '11 at 21:35

score 0 · Accepted Answer · answered Jul 17 '11 at 17:23

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

Why would you do that? The whole point of an outer join is to give you NULLs where you don't have a matching row in the other table. — eaolson, Jul 17 '11 at 23:08

SELECT CASE/JOIN when no rows

3 Answers3