SQLite tricky if join

I have the following two tables:

T1 (id,name) T2 (id,hybrid_col)

What I want to do is select all from T2 and JOIN with T1 if hybrid_col is numeric. Basically, hybrid_col holds an id reference to T1 (in which case I want to get the name from T1) or a text str开发者_如何学Cing (in which case I just want hybrid_col).

How can I do that please?

Thank you.

Another approach is to just UNION the two cases:

SELECT id, hybrid_col
FROM T2
WHERE hybrid_col+0!=hybrid_col
UNION
SELECT T2.id,t1.name
FROM T2 INNER JOIN T1 ON T2.hybrid_col=T1.id;

There is no need to check if hybrid_col is numeric if it has a match with T1.ID. If T1.ID is always numeric, non-numerics will be left out of the join.

EDIT: To sort them, encapsulate the result and sort that:

SELECT ID, VALUE
FROM
(
    SELECT id as "ID", hybrid_col as "Value"
    FROM T2
    WHERE hybrid_col+0!=hybrid_col
    UNION
    SELECT T2.id as "ID",t1.name as "Value"
    FROM T2 INNER JOIN T1 ON T2.hybrid_col=T1.id
) Q
ORDER BY ID;

There are other ways, like piping the result into a temp table and querying that, but the above is probably the simplest one-query approach.

If I understood well, this should be:

SELECT
(
  CASE 
     WHEN T2.HYBRID_COL = T1.ID THEN T1.NAME
      ELSE T2.HYBRID_COL
   END
) AS COLUMN
T2, T1
WHERE T2.ID = T1.ID
and ISNUMERIC(T1.HYBRID_COL ) = 1

Edit : Don't know if you were looking for the ISNUMERIC because the T1.ID is numeric, if it is that case just remove the and ISNUMERIC(T1.HYBRID_COL ) = 1 it should work anyway because the select validates that the T2.HYBDRID_COL matches with the T1.ID

Try this once

select id,
(case when ISNUMERIC(hybrid_col) = 1 then name
else hybrid_col end) as custom_col
from
(select T2.id,T2.hybrid_col,T1.name from T2 left join T1 on T2.id = T1.id) mytab

This should work without using ISNUMERIC (which is unavailable in SQLite)

SELECT IFNULL(T1.name, T2.hybrid_col) AS [name]
FROM T2
    LEFT OUTER JOIN T1 ON CAST(T1.id AS VARCHAR) = T2.hybrid_col

This should work for you, but due to the type cast you will likely lose any benefits from indexing that you might otherwise have on T1.id