[thelist] SQL - select in a join?

Ben Prew ben.prew at gmail.com
Tue Jan 10 11:48:10 CST 2006


On 1/10/06, Canfield, Joel <JCanfield at pacadvantage.org> wrote:
> What is this doing? I don't get why it's selecting multiple columns as
> part of the join. Is it essentially creating a subset of the AGENT_MST
> table on the fly, kind of like a view for use in the join?
>

The extra columns are there so you can use them in the upper select statement.

ie

select a1.first_name, a1.last_name, a1.Intrl_Wrk_GROUP
from
some_table g
left join (
   (
       SELECT A.Agent_Id,A.First_Name,A.Last_Name,A.My_Rep_Id,
       VALUE
           (
               AD.Wrk_GROUP_Id,A.Intrl_Wrk_GROUP
           ) AS Intrl_Wrk_GROUP
       FROM AGENT_MST A
       LEFT JOIN Agent_Dtl AD ON A.Agent_Id = AD.Agent_Id AND
AD.Eff_Till IS NULL
   ) A1
on g.w_agent_id = a1.agent_id

As to your second question, yes, although I'm not sure what MS-SQL or
others call them, but I believe Oracle calls that an "inline view".
(see http://www.dba-oracle.com/tips_oracle_inline_views.htm for a
brief description)

Note: given the example code, you could probably re-write it without
the inline view:

ie:

select a.first_name, a.last_name, a.my_rep_id,
 VALUE
           (
               AD.Wrk_GROUP_Id,A.Intrl_Wrk_GROUP
           ) AS Intrl_Wrk_GROUP
from
some_table g
left join AGENT_MST a
on g.w_agent_id = a.Agent_id
left join
Agent_Dtl on a.Agent_id
where AD.Eff_Tills IS NULL

Although, there may be more of the query that you're not posting that
might make it incompatible.  And, it may change the performance
characteristics of the query, so you wouldn't want to change it
without profiling both version.

In SQL, much like other languages, there is usually more then one way
to do it (TIMTOWDI).


--
--Ben



More information about the thelist mailing list