SELECT DISTINCT study FROM table WHERE keyword='chemistry'; - Of course if it's 'chemistry' it IS <> ''computers' or anything else 'chemistry' <> 'computers' so last part ot your SQL statement is obsolete
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
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'
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:
SELECT study FROM table WHERE keyword = 'chemistry' AND keyword <> 'computers'
returns A, B, C results
SELECT study FROM table WHERE keyword = 'computers' AND keyword <> 'chemistry'
returns A,B
SELECT study FROM table WHERE keyword = 'chemistry' AND NOT keyword = 'computers'
returns 0 results
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.
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.
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 ('+chemestry -omputers IN BOOLEAN MODE)"
What about one field search?
Thank you, Lana
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
