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]
