SQL Server Performance

A sql statement with embedded query in the outer joins..

Discussion in 'Getting Started' started by hindueee, Aug 24, 2007.

  1. hindueee New Member

    Hi,there is a select statement found in the outer joins.
    This is the set of statements. Select * from ms_d_person t1, ms_d_action t2, ms_d_action t3, ms_d_action t4 where t1.client_code = t2.client_code and t1.servicee_id = t2.servicee_id and t3.client_code =* t2.client_code and t3.servicee_id =* t2.servicee_id and t3.case_date_and_time =* t2.case_date_and_time and t3.action_id =* (Select max(action_id) from ms_d_action where client_code = t2.client_code and servicee_id = t2.servicee_id and case_date_and_time = t2.case_date_and_time
    and action_code IN ('EXR','RF','RFI'))
    how to convert this with outer join keyword in sql 2005? thanks in advance.
  2. Adriaan New Member

    Basically, =* translates to a RIGHT JOIN.
    FROM table1, table2 WHERE table1.col =* table2.col
    ... should now be written as ...
    FROM table1 RIGHT JOIN table2 ON table1.col = table2.col
    ... or as ...
    FROM table2 LEFT JOIN table1 ON table2.col = table1.col
    You can simply add the derived table with a JOIN in the FROM clause, with an alias:
    FROM table2 LEFT JOIN (SELECT col FROM othertable) tableX ON table2.col = tableX.col
  3. hindueee New Member

    It is ok with that part.But check with the last line of the query.There is a select statement in the right outer join.
    SO,I want to clarify with that.How to specify that line with outer joins?
  4. Adriaan New Member

    Hm, actually it looks like you can do that subquery in the 'column list', as a correlated subquery. All the aliases should work fine.

Share This Page