At the minute I have a sql query extracting info from an oracle table, and the sql looks like this:
string openSql = "SELECT DISTINCT HGR6.CALL_REQ.PERSID, HGR6.CALL_REQ.REF_NUM, HGR6.CALL_REQ.STATUS, HGR6.CALL_REQ.ASSIGNEE, HGR6.CALL_REQ.OPEN_DATE FROM HGR6.CALL_REQ WHERE HGR6.CALL_REQ.GROUP_ID='" + teamID + "' AND (HGR6.CALL_REQ.OPEN_DATE >= '" + startSeconds.TotalSeconds+"') AND (HGR6.CALL_REQ.OPEN_DATE <= '"+endSeconds.TotalSeconds+"')AND HGR6.CALL_REQ.STATUS <> 'CL' AND HGR6.CALL_REQ.PARENT IS NULL";
At the minute I am binding the results to a datagrid in asp.net, the resulting table looks like this:
PERSID REF_NUM STATUS ASSIGNEE OPEN_DATE
cr:160709287 5992436 RIP 55864293 1195494904
cr:160814117 5999741 ESC 12345678 1195573843
cr:160878008 6004760 ESC 73773767 1195600721
cr:160885707 6005207 ESC 1195610604
Now, the assignee here is sometimes blank - that is ok. But where the assignee is not blank, the assignee is an ID, I need to obtain the persons name from another table using SQL.
Here is the SQL I need to use:
string assigneeSql = "SELECT HGR6.C_LAST_NAME, HGR6.C_FIRST_NAME FROM HGR6.CTCT WHERE HGR6.ID = ' assignne from the list ' ";
is there a way to do this ALL IN ONE SQL statement so it will do this BEFORE I bind it to the Datagrid?