Exclude results where two fields are not of certain values
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.
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, 开发者_Python百科BOO, YUM
But instead, all I'm getting is:
3, BOO, YUM
It appears to be filtering out all records which has 'FOO' as the type, and 'BAR' as the name.
(We're using MySQL 5.1...)
If I understand your question, you should change your query to use OR
:
AND (t1.type != 'FOO' OR t2.name != 'BAR')
This is the same as
AND NOT (t1.type = 'FOO' AND t2.name = 'BAR')
and will remove all rows, where both conditions match.
精彩评论