Opposite of the SQL "IN" statement?
-
Does anyone know if there is an opposite to the SQL "IN" statement? I have a string of values that I want to check the database for. However if I use an IN statement it uses "OR". I want to be able to use an IN statement that uses "AND".. Is there anything out there? Thanks and sorry for the trivial question. Dave:)
-
Does anyone know if there is an opposite to the SQL "IN" statement? I have a string of values that I want to check the database for. However if I use an IN statement it uses "OR". I want to be able to use an IN statement that uses "AND".. Is there anything out there? Thanks and sorry for the trivial question. Dave:)
The opposite of "IN" would be "NOT IN", but I don't think that is what you are after in this case. I don't understand your question wrt. "AND". If I query a column called "NAME", I can have returned all names IN ("MIKE", "CHRIS", "DON"). But there is no way any data item will ever be equal to all three of those values, which is what you are asking for when you say you want an "AND" condition.
Chris Meech I am Canadian. [heard in a local bar] I agree with you that my argument is useless. [Red Stateler] Hey, I am part of a special bread, we are called smart people [Captain See Sharp] The zen of the soapbox is hard to attain...[Jörgen Sigvardsson] I wish I could remember what it was like to only have a short term memory.[David Kentley]
-
Does anyone know if there is an opposite to the SQL "IN" statement? I have a string of values that I want to check the database for. However if I use an IN statement it uses "OR". I want to be able to use an IN statement that uses "AND".. Is there anything out there? Thanks and sorry for the trivial question. Dave:)
It sounds like "IN" is what you want. If I have a list of values, i.e. ('Apple', 'Orange') and I want to know if they are in the FRUIT table, I would write: SELECT Name FROM FRUIT WHERE Name IN ('Apple, 'Orange'). This would pull back all values from the fruit table that are in my list. However, there is no logical way to use this with an "AND", because a string can only match one value. If you're looking for the value 'AppleOrange' you might use the LIKE operator: SELECT Name FROM FRUIT WHERE Name LIKE '%Apple%' This would pull back values "Apple" and "AppleOrange" I don't think there's a way to use the LIKE operator with a list though. Hope that helps a bit.
-
Does anyone know if there is an opposite to the SQL "IN" statement? I have a string of values that I want to check the database for. However if I use an IN statement it uses "OR". I want to be able to use an IN statement that uses "AND".. Is there anything out there? Thanks and sorry for the trivial question. Dave:)
Hi Dave, I think u want to select the rows if table contains all the values in your IN statement otherwise no row should be returned. If this is the case then unluckily there is no direct way to cope with this but fortunately you can accomplish this by SP. for example (pseudoCode) 1. variable boolean allValues 2. loop for all value in your IN statment 3. Variable rowcount = select count(*) from table where column = 'your value' 4. if rowcount = 0 then allvalue = false and exit loop else continue with the loop 5. end loop 6. if allValues = false returned no row else returned allrows for the values in your IN statement. Hope that works if this is what you are trying to explain regards Saud
saud