I see what's happening, Erick.
It's matching all the rows in loc1 and loc2 with the same image id.
It *is* excluding 13128, but image id 1 is still appearing because of
the rows where they match *besides* 13128. For example, 18302 and
actually also 2356 since you're joining a table on itself.
Sounds like what you want is to exclude all the image ids for
locLevel5Id = 13128 ?
Rewrite like so, assuming you have subqueries:
SELECT distinct loc1.imageId
FROM locBridgeImageLocLevel5 as loc1
INNER JOIN
locBridgeImageLocLevel5 as loc2 USING (imageId)
WHERE loc1.locLevel5Id = 2356
AND loc2.imageid NOT IN
(SELECT imageid from locBridgeImageLocLevel5 WHERE locLevel5Id = 13128);
I think I'm understanding your goal!!
Dan
On 10/17/06, Erick Carballo <[EMAIL PROTECTED]> wrote:
Dan, thanks for your prompt response. You are correct: I mistyped.
However, if I ran the query as you suggest, I obtain the same results:
mysql> SELECT distinct loc1.imageId
-> FROM locBridgeImageLocLevel5 as loc1
-> INNER JOIN
-> locBridgeImageLocLevel5 as loc2 USING (imageId)
-> WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id <> 13128);
+---------+
| imageId |
+---------+
| 1 |
| 2 |
| 280 |
| 4 |
| 5 |
| 6 |
| 7 |
...
| 257 |
| 258 |
| 259 |
| 260 |
| 261 |
+---------+
251 rows in set (0.04 sec)
It stills returns imageId 1, eventhough there is a record
(locBridgeImageLocLevel5Id 541) in which imagedId = 1 and locLevel5Id
= 13128:
mysql> select * from locBridgeImageLocLevel5 where imageId = 1;
+---------------------------+---------+-------------+
| locBridgeImageLocLevel5Id | imageId | locLevel5Id |
+---------------------------+---------+-------------+
| 1 | 1 | 2356 |
| 2 | 1 | 18302 |
| 541 | 1 | 13128 |
+---------------------------+---------+-------------+
Erick
At 2:47 PM -0500 10/17/06, Dan Buettner wrote:
>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]
--
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]
