Hello, I would really appreciate your help regarding a query. First,
some background:
The query is being executed on the following table:
mysql> describe locBridgeImageLocLevel5;
+---------------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default
| Extra |
+---------------------------+------------------+------+-----+---------+----------------+
| locBridgeImageLocLevel5Id | int(10) unsigned | NO | PRI | NULL
| auto_increment |
| imageId | int(10) unsigned | NO | |
| |
| locLevel5Id | int(10) unsigned | NO | |
| |
+---------------------------+------------------+------+-----+---------+----------------+
As you may see, this table gathers data from two separate tables
(image and and locLevel5). The locLevel5 table contains data from a
US official list of places which--unfortunately--mixes location names
of counties, cities, and certain geographical places (such as lakes,
national parks, mountains, etc.). This creates a many-to-many
relationship between imageId and locLevel5Id, as the following
queries show:
mysql> select * from locBridgeImageLocLevel5 where imageId = 1;
+---------------------------+---------+-------------+
| locBridgeImageLocLevel5Id | imageId | locLevel5Id |
+---------------------------+---------+-------------+
| 1 | 1 | 2356 |
| 2 | 1 | 18302 |
| 541 | 1 | 13128 |
+---------------------------+---------+-------------+
mysql> select * from locBridgeImageLocLevel5 where imageId = 2;
+---------------------------+---------+-------------+
| locBridgeImageLocLevel5Id | imageId | locLevel5Id |
+---------------------------+---------+-------------+
| 3 | 2 | 2356 |
| 4 | 2 | 18302 |
+---------------------------+---------+-------------+
So far I have been successful in obtaining data from the tables
asking for two discrete criteria using the AND logical operator:
mysql> SELECT distinct loc1.imageId
-> FROM locBridgeImageLocLevel5 as loc1
-> INNER JOIN
-> locBridgeImageLocLevel5 as loc2 USING (imageId)
-> WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id = 18302);
+---------+
| imageId |
+---------+
| 1 |
| 2 |
+---------+
The problem comes when try to find a record in which a condition is
positive, and the other negative. Using NOT gave me a syntax error:
mysql> SELECT distinct loc1.imageId
-> FROM locBridgeImageLocLevel5 as loc1
-> INNER JOIN
-> locBridgeImageLocLevel5 as loc2 USING (imageId)
-> WHERE (loc1.locLevel5Id = 2356 NOT loc2.locLevel5Id = 18302);
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'loc2.locLevel5Id = 18302)' at line 5
And using <> in combination with AND, gave me a result that is not correct.
mysql> SELECT distinct loc1.imageId
-> FROM locBridgeImageLocLevel5 as loc1
-> INNER JOIN
-> locBridgeImageLocLevel5 as loc2 USING (imageId)
-> WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id <> 18302);
+---------+
| imageId |
+---------+
| 1 |
| 2 |
| 280 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
...
| 259 |
| 260 |
| 261 |
+---------+
I should rephrase my last sentence, I am sure the result *IS*
correct, but it is not what I am looking for. I am looking for those
images that are associated with locLevel5Id 2356, but NOT associated
with locLevel5Id 13128. As you can see from the query result above,
imageId 1 is selected, however there is a record
(locBridgeImageLocLevel5Id 541) in which imagedId = 1 and locLevel5Id
= 13128.
As I mentioned before, your help will be greatly appreciated. If you
need further details, please let me know.
Erick
Teaching & Learning Technologies Center
Ballantine Hall 307
http://www.indiana.edu/~tltc/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
