RE: union, intersct and except operation?
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.) INTERSECT and EXCEPT are set operators, similar to UNION, although UNION is the only one currently supported in MySQL (all are ANSI SQL92, but union is most widely supported). Other databases, e.g. Postgres and Oracle among others, do support INTERSECT and EXCEPT. To the best of my knowledge, Michael Stassen's suggestion earlier to use a subquery (meaning you need >=4.1) is the only way out in MySQL, although the left join solution is intriguing, and I'd love to hear more about it. So, SELECT T.data_id from table T WHERE T.keyword = 'chemistry' EXCEPT SELECT T2.data_id from table T2 WHERE T2.keyword = 'computers' Would become SELECT T.data_id FROM table T WHERE T.keyword = 'chemistry' AND NOT EXISTS ( SELECT T2.data_id FROM table T2 WHERE T2.keyword = 'computers' AND T2.data_id = T1.data_id ) For more on this workaround, see: http://www.winnetmag.com/Windows/Article/ArticleID/40321/40321.html http://www-db.stanford.edu/~ullman/fcdb/oracle/my-nonstandard.html#intersect http://www.oracle.com/technology/products/rdb/pdf/new_except.pdf And, if you have it, Joe Celko's "SQL for Smarties," pp. 414-419. Andy Crain NewsLogic, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: union, intersct and except operation?
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 Achemistry
> 2 Acomputers
> 3 Bchemistry
> 4 Bcomputers
> 5 Cchemistry
>
>
> 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]
>
Re: union, intersct and except operation?
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 Achemistry
2 Acomputers
3 Bchemistry
4 Bcomputers
5 Cchemistry
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]
Re: union, intersct and except operation?
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 Achemistry
2 Acomputers
3 Bchemistry
4 Bcomputers
5 Cchemistry
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]
