I don't know if I'm just burnt out and can't think, or what... But I can't seem to make this work right...
(We're using MySQL 5.1...)
I have two tables which have some transactional stuff stored in them. There will be many records per user_id in each table. Table1 and Table2 have a one-to-one relationship with each other. I want to pull records from both tables, but I want to exclude records which have certain values in both tables. I don't care if they both don't have these values, or if just one does, but both tables should not have both values. (Does this make any sense? lol)
For example:
SELECT t1.id, t1.type, t2.name
FROM table1 t1
INNER JOIN table2 t2 ON table.xid = table2.id
WHERE t1.user_id = 100
AND (t1.type != 'FOO' AND t2.name != 'BAR')
So t1.type is type ENUM with about 10 different options, and t2.name is also type ENUM with 2 options.
My expected results would look a little like:
1, FOO, YUM
2, BOO, BAR
3, BOO, YUM
But instead, all I'm getting is:
3, BOO, YUM
Because it's filtering out all records which has 'FOO' as the type, and 'BAR' as the name.
I keep waiting for that D'oh! moment where it hits me and I feel like an idiot for not realizing what I'm doing wrong. But it hasn't come. And I still feel like an idiot, lol. I appreciate any light any of you can shed on this!
Many thanks in advance for the help!
