Is it possible to join two tables on a column that contains a mix of castable and uncastable data?

Question

Apologies if this has been asked before, I combed through a lot of questions but wasn't able to find an answer I could apply to this problem.

I'm putting together an SSRS report from a database I have read-only access to and am having trouble selecting the data in the format I would like.

Table 1: Referenced Data
==== ============= 
 ID   Description  
==== ============= 
 1    Apple        
 2    Orange       
 3    Pear         
==== ============= 

Table 2: Records
==== ====== ======== 
 ID   Data    Tag   
==== ====== ======== 
 1   1      List    
 2   2      List    
 3   Red    String  
 4   Blue   String  
 5   3      List    
==== ====== ========

What I would like to see is:

Report
========== ========= 
 RecordNo   Content  
========== ========= 
    1       Apple    
    2       Orange   
    3       Red      
    4       Blue     
    5       Pear     
========== =========

The problem is the Data column of T2 is nvarchar while the ID column of T1 is bigint. While I can avoid a conversion error by selecting only rows with the List tag, I lose the String data.

Is there any way to do what I'm trying to do in a select statement? It feels like this is something that could be done with some creativity, but I'm still in the learning process and my SQL isn't quite there yet.

Hi Eric, apologies it was SQL Server. – Jared Nov 28 '17 at 18:21 — Jared, Nov 28 '17 at 18:21

score 0 · Answer 1 · answered Nov 27 '17 at 18:01

You could try something like this:

;WITH DataNum AS
(
 SELECT ID, Data FROM Records WHERE ISNUMERIC(Data) = 1
)
SELECT dn.ID, rd.Description
FROM DataNum dn
JOIN ReferencedData rd ON dn.Data = rd.ID
UNION ALL
SELECT ID, Data
FROM Records
WHERE ISNUMERIC(Data) <> 1

score 0 · Accepted Answer · answered Nov 27 '17 at 18:20

0

SELECT
    rec.Id
    ,Content = CASE WHEN data.Id IS NOT NULL THEN data.Description ELSE rec.Data END
FROM
    Records rec
    LEFT JOIN ReferencedData data
    ON rec.Tag <> 'String'
    AND rec.Data = CAST(data.Id AS NVARCHAR(50))

You can cast the BIGINT to NVARCHAR as part of your join condition which will avoid the implicit conversion error.

answered Nov 27 '17 at 18:20

Matt

13,833
2
16
28

Awesome, thanks, Matt. This did the trick. I was so busy racking my brains over the string to bigint conversion error I never even considered casting the bigint instead. And thanks to everyone that took the time to help out with this. – Jared Nov 27 '17 at 19:19

score 0 · Answer 3 · answered Nov 27 '17 at 19:45

select table2.id
     , coalesce(table1.description, table2.Data) content
  from table2
  left join table1
    on table2.tag <> 'String'
   and table1.id = table2.data
 order by table2.id

This works in MySQL (SQL Fiddle) but requires a slight change in SQL Server (SQL Fiddle) and Oracle (SQL Fiddle):

select table2.id
     , coalesce(table1.description,table2.Data) content
  from table2
  left join table1
    on table2.tag <> 'String'
   and table1.id = case when table2.tag <> 'String' then table2.data end
 order by table2.id

and PostgreSQL (SQL Fiddle) needs a further tweek:

select table2.id
    , coalesce(table1.description,table2.Data) as content
  from table2
  left join table1
    on table2.tag <> 'String'
   and cast(table1.id as varchar(6)) = table2.data
 order by table2.id

Is it possible to join two tables on a column that contains a mix of castable and uncastable data?

3 Answers3