[thelist] LEFT JOIN and INNER JOIN

Jon Molesa rjmolesa at consoltec.net
Wed Apr 4 21:54:04 CDT 2007


I was able to get what I wanted and expected by rearranging the tables.

I'm surprised I haven't had any responses as of yet.  Is it because I
didn't explain myself well?  Or have I upset in the group in some
fashion?  Or is everyone really busy?  If I need to work on how I ask a
question please let me know and I'll work on it.

Here's what I have now:

SELECT
count(calls.id) as calls
, calls.groupid
, calls.location
, calls.region
, agents.agentid
, agents.agentname
FROM calls
LEFT JOIN (agentscommissions
     INNER JOIN agents
     ON agentcommissions.agent_id=agents.agentid)
ON calls.groupid=agentcommissions.groupid
WHERE (calls.CommssionCycle BETWEEN 20071201 AND 20071205)
GROUP BY calls.id
, calls.region;

I'm not entirely sure why this worked and the query below did not(or I
just didn't wait long enough) but it looks to give me what I was
expecting.  The agents.groupid field does contain NULL in some instances
if an agent is not assigned to a location and technically this makes
more sense since an agent can have many locations.  So the logic
becomes:

If a location has an agent find the agents commission_rate, name, and id.
Calculate commission pay given the commission_rate.

Else calculate commission for the location.

The agent name and id are after thoughts and not particularly important
to the decision.

I hope no one else has to solve a problem this way, but if you do and
you find this while searching I hope this helps.

* 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
rjmolesa at consoltec.net



More information about the thelist mailing list