Newbie SELECT Question

[Steve Cote]

We are having problems with what we think is a simple select statement:

select ENTITY from ATTRIBUTE
  where (NAME='FavoriteSport' and VALUE='Soccer')
    and (NAME='FavoriteFood' and VALUE='CornDogs');
First, we are running an older version of MySQL:

mysql> select version();
+------------------+
| version()        |
+------------------+
| 3.23.22-beta-log |
+------------------+
1 row in set (0.00 sec)
First, we create a table:

CREATE TABLE ATTRIBUTE
(
  ENTITY int(10) unsigned NOT NULL,
  NAME varchar(64) NOT NULL,
  TYPE varchar(8),
  VALUE text,
  UNIT varchar(12),
  READONLY char(1),
  ENTERED timestamp(14),
  UPDATED timestamp(14),
  PRIMARY KEY (ENTITY,NAME)
);
Next we populate it so it contains the following data:

mysql> select ENTITY, NAME, VALUE from ATTRIBUTE;
+--------+---------------+-------------------------+
| ENTITY | NAME          | VALUE                   |
+--------+---------------+-------------------------+
|    128 | Age           | 7                       |
|    128 | FavoriteFood  | Sandwich                |
|    128 | FavoriteSport | Tennis                  |
|    127 | Age           | 5                       |
|    127 | FavoriteFood  | Peanuts                 |
|    127 | FavoriteSport | Hockey                  |
|    125 | FavoriteFood  | Tacos                   |
|    125 | Age           | 7                       |
|    125 | FavoriteSport | Lacrosse                |
|    124 | FavoriteFood  | Hamburgers              |
|    124 | Age           | 8                       |
|    124 | FavoriteSport | Soccer                  |
|    122 | FavoriteSport | Tennis                  |
|    122 | Age           | 7                       |
|    122 | FavoriteFood  | Sandwich                |
|    118 | FavoriteSport | Soccer                  |
|    118 | Age           | 6                       |
|    118 | FavoriteFood  | CornDogs                |
|    119 | FavoriteSport | Swimming                |
|    119 | Age           | 8                       |
|    119 | FavoriteFood  | Salad                   |
|    121 | FavoriteSport | Hockey                  |
|    121 | Age           | 5                       |
|    121 | FavoriteFood  | Ice Cream               |
+--------+---------------+-------------------------+
24 rows in set (0.01 sec)
Now we want a list of entities that have an attribute named 'FavoriteSport'
and a value of 'Soccer':
mysql> select ENTITY from ATTRIBUTE where NAME='FavoriteSport' and VALUE='Soccer';
+--------+
| ENTITY |
+--------+
|    124 |
|    118 |
+--------+
2 rows in set (0.01 sec)
Now get a list of entities that have an attribute named 'FavoriteFood' and a 
value of 'CornDogs':

mysql> select ENTITY from ATTRIBUTE where (NAME='FavoriteFood' and 
VALUE='CornDogs');
+--------+
| ENTITY |
+--------+
|    118 |
+--------+
1 row in set (0.01 sec)

Apparently ENTITY 118 has both a favorite sport of Soccer and a favorite food
of CornDogs because it appears on the result list for each of the queries.
Finally, let's try to get a list of entities that have both a favorite sport 
of Soccer and a favorite food of CornDogs with just one query:

mysql> select ENTITY from ATTRIBUTE where (NAME='FavoriteSport' and 
VALUE='Soccer') and (NAME='FavoriteFood' and VALUE='CornDogs');
Empty set (0.00 sec)

This isn't what we expect. We should see ENTITY 118 appear in the result list
since the previous two queries returned ENTITY 118. What single query will
return just the records that both sets (NAME & VALUE) of tests?
-Steve



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]