-1

I am trying to "filter" some duplicates inside a table locationrelation. The table looks something like this:

+-----+---------+--------+----------------+--------------+
| id  | version | typeId | fromLocationId | toLocationId |
+-----+---------+--------+----------------+--------------+
|   1 |       0 |      1 |             77 |           76 |
|   2 |       0 |      1 |             78 |           76 |
|   3 |       0 |      1 |             79 |           76 |
|   4 |       0 |      1 |             80 |           76 |
|   5 |       0 |      1 |              2 |          240 |
|   6 |       0 |      1 |              3 |          240 |
|   7 |       0 |      1 |              4 |          240 |
|   8 |       0 |      1 |              5 |          240 |
|   9 |       0 |      1 |              6 |          240 |
|  10 |       0 |      1 |              7 |          240 |
----------------------------------------------------------
| 492 |       0 |      1 |              6 |        10000 |

I am trying to select only the toLocationId which is equal to 240 and appears only once. The problem is that I have some duplicates inside this table, as you can see in the last row. (fromLocationId = 6 appears twice, one time it has toLocationId=240, and the second time has toLocationId=1000).

I've tried the following query:

select fromLocationId, toLocationId
from locationrelation
GROUP BY fromLocationId
HAVING COUNT(toLocationId) = 1;

, but the problem now is that it displays toLocationId with other value than 240. (see the last row)

Example:

+----------------+--------------+
| fromLocationId | toLocationId |
+----------------+--------------+
|              1 |          240 |
|              9 |          240 |
|             11 |          240 |
|             33 |          240 |
|             38 |          240 |
|             42 |          240 |
|             51 |          240 |
|             63 |          240 |
|             67 |          240 |
|             72 |          240 |
|             85 |          240 |
|             97 |          240 |
|             98 |          240 |
|            107 |          240 |
|            121 |          240 |
|            146 |          240 |
|            186 |          240 |
|            196 |          240 |
|            198 |          240 |
|            212 |          240 |
|            228 |          240 |
|            244 |          240 |
|            245 |          240 |
|            251 |          240 |
|            253 |          240 |
|            254 |          240 |
|            257 |          240 |
|            258 |          240 |
|          10000 |          240 |
|          10002 |          240 |
|          10003 |          240 |
|          10008 |          240 |
|          10015 |           67 |
+----------------+--------------+
jarlh
  • 42,561
  • 8
  • 45
  • 63
Tr909
  • 31
  • 3

1 Answers1

-1

Nevermind, I found the solution. A simple and would to the job, but I got an error first time when I used the AND operator. (maybe something wrong in the query)

select fromLocationId,toLocationId from locationrelation GROUP BY fromLocationId HAVING COUNT(toLocationId) = 1 and toLocationId = 240;
Tr909
  • 31
  • 3
  • 1
    This query is invalid and will raise an error when you upgrade to a newer MySQL version. – jarlh Jul 20 '21 at 11:57
  • @jarlh it all depends on the sql mode setting, just the default has been swapped to prevent such queries from happening as opposed to allow. – Shadow Jul 20 '21 at 12:02
  • @Tr909: you do not even need the group by, just a where clause to filter if the above solution works for you. – Shadow Jul 20 '21 at 12:03