How to return T2 name based on data from T1?

T1 game

ID   IDC  POWER  HP      LTIME
01   234  NULL   NULL    -1
02   235  NULL   NULL    -1
03   236  312    213     -1
04   237  111    111      1
05   238  331    111      4

T2 heroes

ID   NAME
234  TOM
235  JEK
236  MAX
237  JIZ
238  ZAK

T1 IDC = ID from T2

I need to return all heroes that have anything except null in POWER or in HP with LTIME != -1

Query should return ID and NAME from T2 for that heroes.

In this example query should return: 236 MAX

I tried this:

SELECT heroes.ID, heroes.name
FROM heroes
WHERE LTIME in ( SELECT * FROM game WHERE LTIME IS NOT NULL and POWER or HP != -1)

Answer

Your WHERE condition in the subquery is not correct. 0 is not the same thing as NULL. And POWER or HP != 0 is not the way you test both columns. You have to write POWER IS NOT NULL AND HP IS NOT NULL.

There’s no LTIME column in the heroes table, so you can’t test that in the main query. The subquery should return IDs, you can test that. But it’s generally better to use JOIN instead of IN.

SELECT h.ID, h.name
FROM heroes AS h
JOIN game AS g on h.id = g.idc
WHERE g.ltime = -1 AND g.power IS NOT NULL AND g.hp IS NOT NULL