> 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" <[EMAIL PROTECTED]> > To: <mysql@lists.mysql.com> > Sent: Sunday, October 14, 2007 7:13 AM > Subject: Help with query... > > > > 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. > > > > Grateful for any hints... > > Anders. > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
