Nested query that requires the first result to be returned

Question

I have 2 tables as such

Table ErrorCodes:

type_code    desc
01           Error101
02           Error99
03           Error120 

Table ErrorXML:

row_index     typeCode
1             87
2             02
3             01

The output should be the description(column desc) of the first matched type_code between the 2 tables

Expected output : Error99

I have gotten so far.

select isnull(descript, 'unknown')  as DESCRIPTION
from (select top 1 a.stmt_cd as descript
    from ErrorCodes a, ErrorXML b
    where a.type_cd = b.typecode
    order by b.row_index)

But this query doesn't return the string UNKNOWN when there is no common typecode (join condition) between the 2 tables. In this case, im getting null.

How can I resolve this?

Answer 1

This is an interesting question. I believe the following can be an intuitive and beautiful solution (I used desc_ as column name rather than desc which is a reserved word):

select (select desc_ from ErrorCodes x where x.type_code = a.typeCode) desc_
    from ErrorXML a
    where (select desc_ from ErrorCodes x where x.type_code = a.typeCode) is not null
    order by row_index 
    limit 1;

If you also need to handle the case if query returns no row then for MySQL, following syntax should suffice. For other databases you can use similar encapsulation with isnull, nvl, etc:

select ifnull((select (select desc_ from ErrorCodes x where x.type_code = a.typeCode) desc_       from ErrorXML a       where (select desc_ from ErrorCodes x where x.type_code = a.typeCode) is not null order by row_index limit 1), 'UNKNOWN');

To test I used following scripts and seems to work properly:

create database if not exists stackoverflow;
use stackoverflow;
drop table if exists ErrorCodes;
create table ErrorCodes
(
    type_code varchar(2),
    desc_ varchar(10)
);
insert into ErrorCodes(type_code, desc_) values
    ('01', 'Error101'),
    ('02',  'Error99'),
    ('03', 'Error120');
drop table if exists ErrorXML;
create table ErrorXML
(
    row_index integer,
    typeCode varchar(2)
);
insert into ErrorXML(row_index, typeCode) values
    ('1', '87'),
    ('2', '02'),
    ('3', '01');

Final-1 quote: While generating your tables try to use same column names as much as possible. I.e. I'd suggest ErrorXML to use type_code rather than typeCode.

Final quote: I choose to use lower letters in SQL since capital letters should be used while emphasizing an important point. I also suggest that style.