Dan, your suggestion is *exactly* what I needed!
Furthermore, because of the use of the subquery, there is no need to
join to table to itself, so the query may be simplified to:
mysql> SELECT distinct loc1.imageId
-> FROM locBridgeImageLocLevel5 as loc1
-> WHERE loc1.locLevel5Id = 2356
-> AND loc1.imageid NOT IN
-> (SELECT imageid FROM locBridgeImageLocLevel5 WHERE
locLevel5Id = 13128);
+---------+
| imageId |
+---------+
| 2 |
| 280 |
| 4 |
| 5 |
| 6 |
| 7 |
...
| 255 |
| 258 |
| 259 |
| 260 |
| 261 |
+---------+
Thank you very much for sharing your expertise.
Erick
At 3:56 PM -0500 10/17/06, Dan Buettner wrote:
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
