Lana, You have been asking this question for quite a while now. I think that you do not have a satisfactory answer yet because I do not believe there is an EXCEPT operator in the MySQL vocabulary. If you could post a link to the page from the MySQL manual that shows this operator, we can help you understand how to use it. Otherwise you need to take a little extra time to explain what you want out of your data as many of us may not be familiar with how the EXCEPT operator works in other database systems. (I know I do not recognize the operator.)
You also need to tell us what version of MySQL you are using as many of the possible suggested solutions to your query problem could use version-dependent features of MySQL (like subqueries). Please post the structure of the table or tables involved in your query. The easiest way to do that is to use the command SHOW CREATE TABLE with the \G option (not the ; option). A sample command would be SHOW CREATE TABLE table \G (documentation here: http://dev.mysql.com/doc/mysql/en/SHOW_CREATE_TABLE.html) Please help us to help you. Thank you for your patience. Shawn Green Database Administrator Unimin Corporation - Spruce Pine "L a n a" <[EMAIL PROTECTED]> wrote on 11/09/2004 08:49:18 PM: > 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] >
