Steve> select ENTITY from ATTRIBUTE where (NAME='FavoriteSport' and
Steve> VALUE='Soccer') and (NAME='FavoriteFood' and VALUE='CornDogs');
Steve> Empty set (0.00 sec)
Just analyze the query. You asked for a record in which
name = 'FavoriteSport' AND name = 'FavoriteFood' which is always false
because name can not be both 'FavoriteSport' and 'FavoriteFood' at the
same time.
One of the possible solutions (not so good) is this:
select a.entity from attribute a, attribute b where a.entity =
b.entity and a.NAME='FavoriteSport' and
a.VALUE='Soccer' and b.NAME='FavoriteFood' and b.VALUE='CornDogs';
or you can upgrade to 4.1 and do this
select entity from attribute where NAME='FavoriteSport' and
VALUE='Soccer' and entity in (select entity from attribute where
NAME='FavoriteFood' and VALUE='CornDogs');
in the near future (i think in 5.x tree) you will be able to intersect
two queries
select ... INTERSECT select ...;
select ENTITY from ATTRIBUTE where NAME='FavoriteSport' and
VALUE='Soccer' INTERSECT select ENTITY from ATTRIBUTE where
NAME='FavoriteFood' and VALUE='CornDogs';
Or you can do it through script or something like that
Ivan
__________________________________
One World, one Web, one Program
-- Microsoft promotional ad
Ein Volk, ein Reich, ein Fuhrer
-- Adolf Hitler
__________________________________
http://alas.matf.bg.ac.yu/~mr02014
____ ___ _ _ _ __ ___ ____ _____
/ __/___ __ ____ | __| _ _ ___ \
/ _/ / . / _\/ \ | _| \ \/ / ._\ Ivan Cukic, Form Eye 2003. \
/_/ /___/_/ /_/_/_/ |___|_\ /\___> web development and design /
<__ / _ _ __ ___ ____ /
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
