SQLite tricky if join

Question

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 string (in which case I just want hybrid_col).

How can I do that please?

Thank you.

Answer 1

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.

Answer 2

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