[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