Can You Do Better Than This?

[Jack Timmons]

I have the following query:

http://pastebin.com/pymZvwjz

Essentially, I want to get all members who expire in the date range
provided (month of September, etc).

Members have multiple memberships, for however long their terms are.

You can't just do the between in the first query as far as I can tell,
because that will get -all- memberships in the range. I want just the
ones where there's no membership expiring after it.

--
Jack Timmons
@_Codeacula

[Jimboidaho]

is MAX(end_date) from the membership table? If so, I would just use
that table as the derived table and move everything else out.

[Jack Timmons]

On Tue, Sep 7, 2010 at 5:16 PM, Jimboidaho <jimbo...@gmail.com> wrote:
> is MAX(end_date) from the membership table?  If so, I would just use
> that table as the derived table and move everything else out.

Bounce it to the outside query?

I figured that MySQL would perform a grouping faster on less data than
having the whole table to do through. (99/Lifetime members, for
example, don't need to be parsed).

I'm Mr. T. and I'm a Nightelf Mohawk (heyyy)

So don't gimme no backtalk.

--
Jack Timmons
@_Codeacula

[Jimboidaho]

What table is end_date in??????

On Sep 7, 4:21 pm, Jack Timmons <codeac...@codeacula.com> wrote:

[Jack Timmons]

On Tue, Sep 7, 2010 at 5:29 PM, Jimboidaho <jimbo...@gmail.com> wrote:
> What table is end_date in??????

`csr`.`memberships```````````````

--
Jack Timmons
@_Codeacula

[Jimboidaho]

I posted on your site.

On Sep 7, 4:30 pm, Jack Timmons <codeac...@codeacula.com> wrote:

[Jack Timmons]

On Tue, Sep 7, 2010 at 6:24 PM, Jimboidaho <jimbo...@gmail.com> wrote:
> I posted on your site.

I'm confused. Which site?

Do I have an active one in any case?

--
Jack Timmons
@_Codeacula

[Jimboidaho]

I tried to post to your link but it didn't work. Here is what I
have. I change the derived table to use just memberships so the rest
of the tables are only linking to the results of "where years != 99".
Might make it run a little faster. I have never worked with mySQL
before so there may be a syntax problem???

SELECT `EmailAddress`.address, `Member`.`id`, `Member`.first_name,
`Member`.last_name, `Membership`.expiration
FROM
(SELECT mm.member_id,MAX(mm.end_date) AS expiration
FROM `csr`.`memberships` as mm
WHERE mm.years != 99
GROUP BY mm.member_id
) as Membership
JOIN `csr`.`members` AS `Member`
ON `Membership`.member_id = `Member`.id
JOIN `csr`.`email_addresses` AS `EmailAddress`
ON `EmailAddress`.`ref_id` = `Member`.id
AND `Member`.flag = 0
AND `Member`.receive_emails = 0
AND `EmailAddress`.address != ''
WHERE Membership.expiration BETWEEN '2010-%02d-01' AND '2010-%02d-31'
LIMIT 10

On Sep 7, 5:27 pm, Jack Timmons <codeac...@codeacula.com> wrote:

[Jack Timmons]

On Tue, Sep 7, 2010 at 7:18 PM, Jimboidaho <jimbo...@gmail.com>
explained in detail:

> I tried to post to your link but it didn't work.  Here is what I
> have.  I change the derived table to use just memberships so the rest
> of the tables are only linking to the results of "where years != 99".
> Might make it run a little faster.  I have never worked with mySQL
> before so there may be a syntax problem???
>
> SELECT `EmailAddress`.address, `Member`.`id`, `Member`.first_name,
> `Member`.last_name, `Membership`.expiration
>    FROM
>    (SELECT mm.member_id,MAX(mm.end_date) AS expiration
>        FROM `csr`.`memberships` as mm
>            WHERE mm.years != 99
>          GROUP BY mm.member_id
>    ) as Membership
>    JOIN `csr`.`members` AS `Member`
>        ON `Membership`.member_id = `Member`.id
>    JOIN `csr`.`email_addresses` AS `EmailAddress`
>        ON `EmailAddress`.`ref_id` = `Member`.id
>        AND `Member`.flag = 0
>        AND `Member`.receive_emails = 0
>        AND `EmailAddress`.address != ''
>        WHERE Membership.expiration BETWEEN '2010-%02d-01' AND '2010-%02d-31'
> LIMIT 10

I'll give that a shot in the mornin' and post back the results.

Did you check your TCP/IP settings?

Want this dog?

--
Jack Timmons
@_Codeacula

[Jack Timmons]

On Tue, Sep 7, 2010 at 7:18 PM, Jimboidaho <jimbo...@gmail.com> wrote:
> Query stuff

Final query:

http://pastebin.com/V22pYM8N

Thanks for the inspiration!

--
Jack Timmons
@_Codeacula