[thelist] LEFT JOIN and INNER JOIN

[Jon Molesa]

Should have included more information:

agentcommissions contains agent_id, location, commission1, commission2

agents contains agnetid, agentname

calls contains location, region, CommissionCycle, id, and various other
fields.

What needs to happen is that agents "own" a location.  The commissions
are location specific and split between the agent and the location.
There are two "plans" that a call could be placed on.

The logic is something like:

If the location has an agent pull agents commission rate for that plan
and give the rest to the location

Else give all the commission for that plan to the property

I'm trying to match the agent assigned to the location and pull in their
commission rate, but if no agent I still want to see the details for
that location, thus the LEFT JOIN. 

I hope this helps clear up what I 'm trying to do. 

* Jon Molesa <rjmolesa at consoltec.net> wrote:

> I saw some interesting discussion on here earlier in reference to some
> joins.  I hope to catch up on that tonight when I get home.
> 
> My question is:  Can you you have....
> 
> Well scape that MySQL just responded to me.
> 
> Now the question is:  Is there a way to optimize a LEFT JOIN INNER JOIN
> statement?
> 
> example:
> 
> SELECT
> count(calls.id)
> , calls.location
> , calls.region
> , agents.agentid
> , agents.agentname
> FROM calls
> LEFT JOIN (agents
>     INNER JOIN agentcommissions
>     ON agents.agentid=agentcommissions.agent_id)
> ON calls.groupid=agents.groupid
> WHERE (calls.CommssionCycle BETWEEN 20071201 AND 20071205)
> GROUP BY calls.id, calls.region;
> 
> When I ad the INNER JOIN it triples the run time of the query and
> returns only the first record of the agents table as the value
> in agents.agentid and agents.agentname.  It is very strange.  Perhaps
> someone could explain the order of operations to me?  Any insight is
> appreciated.
> 
> 
> -- 
> Jon Molesa
> rjmolesa at consoltec.net
> -- 
> 
> * * 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 ! 

-- 
Jon Molesa
Owner - Consoltec
336.844.4104
828.994.2067
866.433.0835
rjmolesa at consoltec.net
http://www.consoltec.net