[thelist] SQL - select in a join?

[Tab Alleman]

Is this TSQL?   I don't see VALUE() in BOL....?

> -----Original Message-----
> From: thelist-bounces at lists.evolt.org
> [mailto:thelist-bounces at lists.evolt.org]On Behalf Of Matt Warden
> Sent: Tuesday, January 10, 2006 12:48 PM
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] SQL - select in a join?
> 
> 
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Canfield, Joel 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?
> > 
> > thanks
> > 
> > joel
> > 
> > ------------------------
> > 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 far as I can tell, there is no reason to select those extra columns
> in A. However, what it is doing is joining G and A1 on Agent_Id, where
> A1 is the "non-joined" product of AGENT_MST and Agent_Dtl 
> over Agent_Id.
> 
> The author seemingly was trying to get all records from the 
> nested join
> which *did not match* the join condition (that's the left 
> join plus the
> filter on null). He/she is then matching the records in AGENT_MST
> without a matching record in Agent_Dtl to records in G 
> (whatever that is).
> 
> So, to completely stab in the dark here, let's say that 
> Agent_Dtl holds
> assignments for agents. This query might then be finding all 
> agents not
> currently assigned and matching them against, say, a contact 
> information
> table. So, it would read something like: Get me the contact 
> information
> of all agents who do not currently have an assignment.
> 
> That's probably not really what this is doing, but hopefully it helps
> you grasp the concept of what's going on.
> 
> - --
> Matt Warden
> Miami University
> Oxford, OH, USA
> http://mattwarden.com
> 
> 
> This email proudly and graciously contributes to entropy.
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (GNU/Linux)
> Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
> 
> iD8DBQFDw/NArI3LObhzHRMRAk/xAKDsqx6WapV79Ba2HmgO4aAnTeodOgCfUr9e
> JKwSX9B60Dkr+Uf70dYRRC4=
> =Oh8X
> -----END PGP SIGNATURE-----
> -- 
> 
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester 
> and archives of thelist go to: http://lists.evolt.org 
> Workers of the Web, evolt ! 
>