List:General Discussion« Previous MessageNext Message »
From:Anders Norrbring Date:October 14 2007 4:54pm
Subject:SV: Help with query...
View as plain text  
> 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@stripped>
> To: <mysql@stripped>
> 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/mysql?unsub=1
> >
> >

Thread
SV: Help with query...Anders Norrbring14 Oct