Erick, maybe I'm missing something or you mistyped, but you appear to be saying this: you want 2356 and not 13128 but your last SQL query is excluding only 18302. 13128 is not mentioned in the query.
Try re-running the query with 13128 instead of 18302 ? Dan On 10/17/06, Erick Carballo <[EMAIL PROTECTED]> wrote:
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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]