Selecting a default value from table if row does not exist

Question

I have two tables - Table Records with columns ID, Code,ProviderId Table Codes with columns Code, Number, ProviderId

Sample Data:

Table Records

 ID             Code         ProviderId
 1              ABC           1 
 2              DEF           2
 3              XYZ           1
 4              PQR           2

Table Codes

 Code       Number     ProviderId
 ABC        1111        1
 Default    9999        1 
 XYZ        2222        2
 Default    4444       2

All the rows in Records table will have a code. Codes table will have a set of codes defined with other information. It is not necessary that all the codes in Records table will have an entry in Codes table. For Code in Records table if corresponding value exists then select the same else need to select Default code based on the ProviderID column.

My expected result is:

ID      Code     Number
-------------------------
1       ABC      1111
2       DEF      9999 -> Picked up the default for ProviderId 1
3       XYZ      2222
4       PQR      4444 -. Picked up the default for ProviderId 2

I was able to achieve this using left outer join to add the records into a table variable and then again performing an inner join. I was wondering if I can achieve this in a single select.

Ben10 : But DEF has providerID = 2. Hence shouldn't it return default for ProviderId 2 i.e 4444 — Paresh J
– Paresh J, Commented Nov 10, 2014 at 5:13

Gordon Linoff · Accepted Answer · 2014-11-10 03:35:14Z

2

You can do this with left joins:

select r.id, r.code, coalesce(c.number, cd.number) as number:
from records r left join
     codes c
     on r.code = c.code left join
     codes cd
     on r.providerid = c.providerid and c.code = 'Default';

That is, lookup both values. Choose the one based on the code if there is a match; otherwise, use the default.

answered Nov 10, 2014 at 3:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ajay2707 · Accepted Answer · 2014-11-10 05:14:48Z

I think, your 'DEF' or 'PQR' entry is wrong, eigher one should have "Providerid" is 1. I prepare a sample from your data.

Use some code of @Gordon. but logic is different.

declare @records table(id int, code varchar(50), providerid int)
declare @code table(code varchar(50), number int, providerid int)

insert into @records values (1,'ABC',1), (2,'DEF',2),(3,'XYZ',1),(4,'PQR',2)

insert into @code values ('ABC',1111,1 ), ('DEFAULT',9999, 1) , ('XYZ',2222,2) , ('Default', 4444, 2)

--your data in wrongly entered , that why the 'DEF' show the wrong thing below.
select 
    id, r.code ,
     case 
        when ISNULL(c.code , '') = '' 
        then c1.number
        else c.number
    end
from 
    @records r
    left outer join @code c on r.code = c.code
    left outer join @code c1 on r.providerid = c1.providerid and LOWER( c1.code) = 'default'

--if the default entry is multiple times, then good to use sub-query with top   
select 
    id, r.code ,
    case 
        when ISNULL(c.code , '') = '' 
        then ( select top 1 number from @code c1 where r.providerid = c1.providerid and LOWER( c1.code) = 'default' )
        else c.number
    end
from 
    @records r
    left outer join @code c on r.code = c.code

Pரதீப் · Accepted Answer · 2014-11-10 05:31:37Z

0

Try this.. As mentioned by others 'PQR' entry is wrong in your OUTPUT. It should not be '9999'. It should be '4444'

SELECT a.ID,b.Code,b.Number
FROM   Records a
       JOIN code b
         ON a.Code = b.Code
UNION ALL
SELECT a.ID,a.Code,b.Number
FROM   Records  a
       JOIN code b
         ON a.ProviderId = b.ProviderId
WHERE  NOT EXISTS(SELECT 1
                  FROM   Code aa
                  WHERE  aa.Code = a.Code)
       AND b.Code = 'default'

edited Nov 10, 2014 at 5:31

answered Nov 10, 2014 at 5:25

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Comments

PP006 · Accepted Answer · 2014-11-10 05:47:41Z

0

SELECT A.ID,A.Code,B.Number,A.ProviderId
FROM   #Source A
JOIN   #Code B ON A.Code = B.Code
UNION
(SELECT A.ID,A.Code,B.Number,A.ProviderId
FROM   #Source A
JOIN   #Code B ON A.Code != B.Code
              AND B.Code = 'Default'
WHERE  A.Code NOT IN (SELECT A.Code
                      FROM   #Source A
                      JOIN   #Code B ON A.Code = B.Code)
   AND A.ProviderId = B.ProviderId )

Try using this

answered Nov 10, 2014 at 5:47

PP006

7097 silver badges17 bronze badges

Collectives™ on Stack Overflow

Selecting a default value from table if row does not exist

Sample Data:

Table Records

Table Codes

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Sample Data:

Table Records

Table Codes

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related