Select all rows except based on two fields in MySQL

Question

SELECT * FROM table WHERE id != 4;
SELECT * FROM table WHERE NOT id = 4;
SELECT * FROM table WHERE id <> 4;

I've got this all working but I also have to choose another field (or more fields) to decide what rows are returned.

How can I get this working?

You're already selecting all fields by using * - so I'm not clear on what your question is. And if you're asking about the difference bewteen those three methods of checking for inequality, then refer to this answer: http://stackoverflow.com/questions/7884/testing-for-inequality-in-t-sql — kinakuta, Jun 16 '11 at 04:14
the wording of your question was a little unclear so I tried to clarify. Please let me know if I've stuffed it up so it can be fixed. — paxdiablo, Jun 16 '11 at 04:21

paxdiablo · Accepted Answer · 2011-06-16T04:23:18.260

1

If you want to 'deselect' columns where both conditions are true (ID1 is 4 and ID2 is 7), use something like:

select * from TBL where ID1 <> 4 or ID2 <> 7;

ID1  ID2  selected
---  ---  --------
 4    7     no
 4    1     yes
 1    7     yes
 1    1     yes

If you want to 'deselect' columns where either condition is true (ID1 is 4 or ID2 is 7), use something like:

select * from TBL where ID1 <> 4 and ID2 <> 7;

ID1  ID2  selected
---  ---  --------
 4    7     no
 4    1     no
 1    7     no
 1    1     yes

This can be extended to more conditions simply by adding them to the end of the where clause (and changing both/either to all/any in the text).

edited Jun 16 '11 at 04:23

answered Jun 16 '11 at 04:15

paxdiablo

1

`where both conditions are true` implies `AND` – zerkms Jun 16 '11 at 04:16
2

@zerk: except we're _deselecting_ them, not selecting them - that inverts the condition - I'll add the truth tables to clarify. – paxdiablo Jun 16 '11 at 04:17
I'll accept this as an answer cause this were I get the idea.thank you so much. I have posted an answer to my question though. – woninana Jun 16 '11 at 04:27

score 0 · Answer 2 · answered Jun 16 '11 at 04:26

0

select * from albums where idAlbum!= 4 and idAlbum!= 8 I just solve my problem. Thanks for the help guys!

answered Jun 16 '11 at 04:26

woninana

2 Answers2