L a n a wrote:
Hello,
I've had a problem trying to get working except operator in mysql:
statement "SELECT study from table WHERE keyword = 'chemistry' NOT keyword = 'computers'" returns SQL error
Of course. NOT is an operator, not a connector. That is, "NOT keyword = 'computers'" has the opposite boolean value of "keyword = 'computers'". You still need to connect it to the rest of your conditions with AND or OR.
I've got the following not working solutions to my problem:
SELECT [distinct] study FROM table WHERE keyword = 'chemistry' AND keyword <>(or !=) 'computers'
SELECT study FROM table WHERE keyword = 'chemistry' AND NOT (or OR NOT) keyword = 'computers'
Adding "AND keyword != 'computers'" to "WHERE keyword = 'chemistry'" is pointless. Any row with keyword = 'chemistry' cannot have keyword = 'computers'.
To explain better what result I need to get, there is table
Id Study keyword 1 A chemistry 2 A computers 3 B chemistry 4 B computers 5 C chemistry
I need to return study C, because it has only 'chemistry' keyword(not 'computers'). However, all the suggested solutions return me wrong results:
So, you want to choose a Study (not a row) based on looking at *all* the rows with a particular Study value.
SELECT study FROM table WHERE keyword = 'chemistry' AND keyword <> 'computers'
returns A, B, C results
with Id = 1, 3, 5. That is, the rows with keyword = 'chemistry'.
SELECT study FROM table WHERE keyword = 'computers' AND keyword <> 'chemistry'
returns A,B
results in Ids 2 and 4, the rows with keyword = 'computers'.
SELECT study FROM table WHERE keyword = 'chemistry' AND NOT keyword = 'computers'
returns 0 results
Of course. keyword cannot have 2 different values **in the same row**. Hence, no row matches.
I tried "SELECT T.data_id from table T WHERE T.keyword = 'chemistry' EXCEPT SELECT T2.data_id from table T2 WHERE T2.keyword =computers" . But it gives SQL error.
Right, this is not valid syntax.
I, also, tried "SELECT * from table as t1 left join table as t2 on t1.study=t2.study and not t2.keyword ='computers' where t1.keyword = 'chemistry' " - gives SQL error as well.
I doubt that. This is valid syntax, though it doesn't do what you want. It's close, though. See below.
Is there a way to get exception (not) operator working in mysql at all, does it mean that only Union (or) and Intersect (and) available?
I know that in fulltext search it's possible to do: sql = "SELECT ... MATCH...AGAINST ('+chemistry -computers IN BOOLEAN MODE)"
No, that selects a *row* which contains 'chemistry' but not 'computers' in the fulltext-indexed columns. It does not compare values in one row to values in another.
What about one field search?
Thank you, Lana
One solution is similar to your above LEFT JOIN:
SELECT * FROM table t1 LEFT JOIN table t2 ON t1.Study=t2.Study AND t2.keyword='computers' WHERE t1.keyword='chemistry' AND t2.id is null;
That may seem a little strange, as we are asking for the opposite of what we want on the right side of the join, but then we only take the rows from the left which don't have a row on the right.
Another way to look at all the rows with a particular Study value would be to GROUP BY Study and use aggregate functions. Something like
SELECT Study
FROM table
GROUP BY Study
HAVING SUM(IF(keyword='chemistry',1,0))
AND NOT SUM(IF(keyword='computers',1,0));should do the trick.
If you like subqueries and have mysql 4.1, the following should also work:
SELECT * FROM table
WHERE keyword = 'chemistry'
AND Study NOT IN (SELECT Study FROM table t2
WHERE t2.keyword = 'computers');Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
