[thelist] SQL - select in a join?

Matt Warden mwarden at gmail.com
Tue Jan 10 11:47:44 CST 2006

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
> ------------------------
>     (
>         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

This email proudly and graciously contributes to entropy.
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org


More information about the thelist mailing list