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?

Mehmet Kaplan · Accepted Answer · 2017-09-03T15:59:27.497

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.

Thanks for the response. In the above query, if errorXml has typecodes as 99,12,11. Then the expected reult should be UNKNOWN. How can i achieve this? This is where I'm stuck at — keanu, Sep 03 '17 at 15:07
@keanu please check updated answer. There is a new select statement right after the original one. — Mehmet Kaplan, Sep 03 '17 at 16:00
you are my favourite person today, my friend. This query is beautiful. Thanks a ton!! — keanu, Sep 03 '17 at 16:46
Then you can upvote and accept the answer. Enjoy, cheers. ;-) — Mehmet Kaplan, Sep 03 '17 at 16:56

score 0 · Answer 2 · edited Sep 03 '17 at 07:41

What about this: Do a subquery to bring back the first row_index for each type_code. Do a LEFT OUTER Join on the ErrorCodes table so that you get NULLs as well.

SELECT 
    ISNULL(ErrorCodes.desc,'unknown') AS description
    ErrorXML.row_index
FROM ErrorCodes 
LEFT OUTER JOIN (
                 SELECT type_code, MIN(row_index) AS row_index 
                 FROM ErrorXML
                 GROUP BY type_code
                ) AS ErrorXML ON ErrorCodes.type_code = ErrorXML .type_code

Nested query that requires the first result to be returned

2 Answers2