[SOLVED] comparing two tables but with two stipulations

[c_shelswell]

Hi i'm trying to compare two tables in mysql i'm just looking for "users who are not in table1 but are in table2 and the date range from table1 that should be searched is from 2007-01-04 to 2007-05-01"

 

here's the actual sql

 

$q = " select reg_users.email from reg_users inner join purchases on reg_users.username=purchases.username where purchases.username is null and purchases.date between '$sDateOne' and '$eDateOne'";

 

If i don't add the date bit my query works fine, sending back everyone who has not purchased it's just when i try to search in the date range it starts to return 0 rows.

 

Any idea how i might fix this??

 

Thanks Very much

[obsidian]

You can always just use a nested select statement:

SELECT email 
FROM reg_users
WHERE username NOT IN (
  SELECT username 
  FROM purchases
  WHERE date BETWEEN '$stDate' AND '$edDate'
);

 

Hope this helps.

[c_shelswell]

Brilliant thanks very much. I had been trying nested statements but just wasn't managing to figure it out.

 

 

Thanks again