Re: Newbie SELECT Question

[Hans-Peter Grimm]

Steve Cote wrote:
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');
You are trying to find a row with a NAME value of 'FavoriteSport' and 
'FavoriteFood' at the same time, also with a VALUE value of 'Soccer' and 
'CornDogs' at the same time. This can't work.

Here's a query that does what you want:

SELECT a1.ENTITY FROM ATTRIBUTE a1, ATTRIBUTE a2
WHERE a1.ENTITY = a2.ENTITY
  AND a1.NAME='FavoriteSport' and a1.VALUE='Soccer'
  AND a2.NAME='FavoriteFood' and a2.VALUE='CornDogs'

[...]
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?
[...]




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