 | | | Help with query... | Help with query... 2007-10-14 - By mysql@(protected)
Back
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 help 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
|