I'm trying to set up a query, but I don't really get the result I
expected, so can someone please me out here?

The query I've built is this:

SELECT a1.username FROM accountuser AS a1
LEFT JOIN (freeaccounts AS f1, payments AS p1)
ON (a1.username = p1.username
AND p1.username = f1.username)
WHERE a1.username LIKE 'cit%'
AND a1.imp + a1.pp + a1.se + a1.auth != 0
AND (f1.free IS NULL OR f1.free = false)
AND (p1.validdate < UNIX_TIMESTAMP(NOW()) OR p1.validdate IS NULL)

The accountuser table is always fully populated.
The freeaccounts and payments tables are only occupied with the
'username' field if it's been used previously, so they may not contain
any data.

What I expect to get from the query is 'username' from the accountuser
table when:

The a1.username starts with 'cit',
AND
Any of a1.imp, a1.pp, a1.se or a1.auth is not 0,
AND
f1.free is either not populated or false.
AND
p1.validdate is either not populated, or the timestamp is before NOW.

Everything seems to work except for the passed time check. If I set
p1.validdate to a timestamp for something next week, the username is
still returned in the result.

Grateful for any hints...
Anders.

Anders Norrbring wrote:
> I'm trying to set up a query, but I don't really get the result I
> expected, so can someone please me out here?
>
> The query I've built is this:
>
> SELECT a1.username FROM accountuser AS a1
> LEFT JOIN (freeaccounts AS f1, payments AS p1)
> ON (a1.username = p1.username
> AND p1.username = f1.username)
> WHERE a1.username LIKE 'cit%'
> AND a1.imp + a1.pp + a1.se + a1.auth != 0
> AND (f1.free IS NULL OR f1.free = false)
> AND (p1.validdate < UNIX_TIMESTAMP(NOW()) OR p1.validdate IS NULL)
>
> The accountuser table is always fully populated.
> The freeaccounts and payments tables are only occupied with the
> 'username' field if it's been used previously, so they may not contain
> any data.
>
> What I expect to get from the query is 'username' from the accountuser
> table when:
>
> The a1.username starts with 'cit',
> AND
> Any of a1.imp, a1.pp, a1.se or a1.auth is not 0,
> AND
> f1.free is either not populated or false.
> AND
> p1.validdate is either not populated, or the timestamp is before NOW.
>
> Everything seems to work except for the passed time check. If I set
> p1.validdate to a timestamp for something next week, the username is
> still returned in the result.
>

Put the last line (the tests on p1.validdate) in the JOIN condition instead.

brian

> Hello
> where is the FROM for f1 table?
> Martin--

Why would there be any "FROM" for the f1 table? It's not needed in the result.

Anyway, I've come up with a query that actually seems to do what I'm looking for;

SELECT a1.username FROM accountuser AS a1
LEFT JOIN payments AS p1 ON (a1.username = p1.username)
LEFT JOIN freeaccounts AS f1 ON (a1.username = f1.username)
WHERE a1.username LIKE 'cit%'
AND a1.imp + a1.pp + a1.se + a1.auth != 0
AND (p1.validdate < UNIX_TIMESTAMP(NOW()) OR p1.validdate IS NULL)
AND (f1.free = false OR f1.free IS NULL)

Thanks for the hints, you who gave them..

Anders.

> ----- Original Message -----
> From: "Anders Norrbring" <lists@norrbring.se>
> To: <mysql@lists.mysql.com>
> Sent: Sunday, October 14, 2007 7:13 AM
> Subject: with query...
>
>
> > I'm trying to set up a query, but I don't really get the result I
> > expected, so can someone please me out here?
> >
> > The query I've built is this:
> >
> > SELECT a1.username FROM accountuser AS a1
> > LEFT JOIN (freeaccounts AS f1, payments AS p1)
> > ON (a1.username = p1.username
> > AND p1.username = f1.username)
> > WHERE a1.username LIKE 'cit%'
> > AND a1.imp + a1.pp + a1.se + a1.auth != 0
> > AND (f1.free IS NULL OR f1.free = false)
> > AND (p1.validdate < UNIX_TIMESTAMP(NOW()) OR p1.validdate IS NULL)
> >
> > The accountuser table is always fully populated.
> > The freeaccounts and payments tables are only occupied with the
> > 'username' field if it's been used previously, so they may not
> contain
> > any data.
> >
> > What I expect to get from the query is 'username' from the
> accountuser
> > table when:
> >
> > The a1.username starts with 'cit',
> > AND
> > Any of a1.imp, a1.pp, a1.se or a1.auth is not 0,
> > AND
> > f1.free is either not populated or false.
> > AND
> > p1.validdate is either not populated, or the timestamp is before NOW.
> >
> > Everything seems to work except for the passed time check. If I set
> > p1.validdate to a timestamp for something next week, the username is
> > still returned in the result.
> >
> > Grateful for any hints...
> > Anders.
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=mgainty@hotmail.com
> >
> >