mysql中不同查询的多个表

Question

Originally my sql query works to check if there is pending timesheet/overtime ready for manager to review... now i added in on-call timesheets which is on a different table...

my previous sql query that worked but WITHOUT $ONCALL_TABLE...

list($qh, $num) = dbQuery("SELECT manager FROM $USER_TABLE WHERE username IN (SELECT DISTINCT uid FROM $TIMES_TABLE WHERE submitstatus=1 OR ot_status=1)");

---

now im trying to add the pending on-call timesheet to the same sql query but i keep getting errors... maybe someone can help me fix my syntax?:

list($qh, $num) = dbQuery("SELECT manager FROM $USER_TABLE WHERE username IN (SELECT DISTINCT $TIMES_TABLE.uid, $ONCALL_TABLE.uid FROM $TIMES_TABLE, $ONCALL_TABLE WHERE $TIMES_TABLE.submitstatus=1 OR $TIMES_TABLE.ot_status=1 OR $ONCALL_TABLE.submitstatus=1)");

ERROR I GET: Can't perform query: Operand should contain 1 column(s)

Answer 1

The problem is in the subquery, but I would handle it using a union all:

SELECT manager
FROM $USER_TABLE
WHERE username IN (SELECT uid
                   from (select $TIMES_TABLE.uid
                         from $TIMES_TABLE
                         where $TIMES_TABLE.submitstatus=1 OR $TIMES_TABLE.ot_status=1
                         union all
                         select $ONCALL_TABLE.uid
                         from $ONCALL_TABLE
                         where $ONCALL_TABLE.submitstatus=1
                        ) t
                   ) 

To be honest, though, the union all may interfere with with the use of indexes. This might perform better:

SELECT manager
FROM $USER_TABLE
WHERE username IN (select $TIMES_TABLE.uid
                   from $TIMES_TABLE
                   where $TIMES_TABLE.submitstatus=1 OR $TIMES_TABLE.ot_status=1
                  ) or
      username in (select $ONCALL_TABLE.uid
                   from $ONCALL_TABLE
                   where $ONCALL_TABLE.submitstatus=1
                  )