Select data that doesn't appear in two tables

[dtyson2000]

Hello. I'm practicing working with two tables. I'd like to be able to select data from both and return only the rows that DO NOT have data in both. I'm just using today's date as the "kicker". If the userid appears in both tables and table 2 has today's date, those rows don't count. If there's no entry in table 2 with a userid and today's date from table 1, i'd like to return the info from table 1.

 

table 1

ID	|	userid
1	|	dave123
2	|	sandy456
3	|	joe789

table 2

ID	|	userid		|	date
1	|	dave123		|	4/5/2011
2	|	sandy456	|	4/5/2011

 

I'm incredibly stuck on how to return only "joe789". It's probably something totally elementary. Do I need to use some sort of JOIN?

 

Thanks for all your support that I've read and learned from while lurking around!

[kickstart]

Hi

 

If you want data that is in one but not in the other then you can use something like this:-

 

SELECT *
FROM table1
LEFT OUTER JOIN table2
ON table1.userid = table2.userid
WHERE table2.userid IS NULL

 

To get any record that is on table1 or table2 but not on both can be done in some flavours of SQL using a FULL OUTER JOIN. But MySql doesn't support full outer joins. Probably easiest to use a UNION:-

 

SELECT *
FROM table1
LEFT OUTER JOIN table2
ON table1.userid = table2.userid
WHERE table2.userid IS NULL
UNION
SELECT *
FROM table1
RIGHT OUTER JOIN table2
ON table1.userid = table2.userid
WHERE table1.userid IS NULL

 

All the best

 

Keith