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: stackoverflow.com/questions/7884/… — kinakuta
– kinakuta, Commented Jun 16, 2011 at 4: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
– paxdiablo, Commented Jun 16, 2011 at 4:21

paxdiablo · Accepted Answer · 2011-06-16 04:23:18Z

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).

answered Jun 16, 2011 at 4:15

paxdiablo

888k243 gold badges1.6k silver badges2k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

where both conditions are true implies AND

@zerk: except we're deselecting them, not selecting them - that inverts the condition - I'll add the truth tables to clarify.

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 · Accepted Answer · 2011-06-16 04:26:15Z

0

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

answered Jun 16, 2011 at 4:26

woninana

3,48911 gold badges45 silver badges69 bronze badges