Evaluating Dates in Query

[dtyson2000]

I need a fresh set of eyes here. I've screwed around with this query for too long trying to figure it out on my own. I'm just not as good as you people are. Thanks to the help of Kickstart, I've grown somewhat used to JOINS but this query is killing me. Here are the details:

table1

id	|	userid	|	name
---	|	---		|	---
1	|	1234	        |	Joe
---	|	---		|	---
2	|	5678 	|	Amy
---	|	---		|	---
3	|	9012	        |	Paul
---	|	---		|	---
4	|	3456	        |	Michele


table2

id	|	t2userid	|	date
---	|	---		|	---
1	|	5678	        |	2011-05-29
---	|	---		|	---
2	|	9012 	|	2011-05-23
---	|	---		|	---
3	|	5678 	|	2011-05-23
---	|	---		|	---
4	|	5678 	|	2011-05-22


I have two tables, each with a common userid. User id's are taken from table 1 and entered into table 2 with the addition of a date.

I would like to:

1) Query both tables (currently using a LEFT OUTER JOIN on table1.userid = table2.t2userid)
2) Return from table2 those userid's that DO NOT have today's date
3) Return ONLY ONCE from table2 those userid's that are less than today's date (unless they also have an entry with today's date - see number 1)
4) Return userid's that are not in table2 (currenly working table2.t2userid IS NULL)


Current query (with a PHP variable supplying $today, supposing today is 2011-05-29): 

SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.userid = table2.t2userid WHERE table2.date != ".$today." AND !(table2.date < ".$today.") OR table2.t2userid IS NULL

What I am getting:

Joe
---
Amy
---
Amy
---
Amy
---
Paul
---
Michele


What I would like to get:

Joe
---
Michele

 

 

Sorry if that seems convoluted. I'm just a little frustrated. Thanks for your earlier help and for taking the time to read! I hope to keep learning from you folks!

[fenway]

I'm guessing you're missing parentheses around some of those WHERE expressions -- matters with OR.

[dtyson2000]

Thanks, Fenway, for your input. I played around and played around with all sorts of parenthesis placement and came across an article that pointed me in a different direction. So far, it seems to work. Here's what I came up with:

SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.userid = table2.t2userid WHERE (DATE(table2.date) != CURDATE()) && (DATE (!(table2.date) < CURDATE())) || table2.t2userid IS NULL

 

How I got there... I'm still scratching my head. 

 

Thanks again!