MYSQL - WHERE values in two columns

Question

i have a table like this :

id | node_source    | node_destination | path
1  |        1       |       0          | {"coordinates": [[-6.27400693507...
2  |        0       |       1          | {"coordinates": [[-6.24568104953...
3  |        1       |       2          | {"coordinates": [[-6.24568104953...
4  |        2       |       1          | {"coordinates": [[-6.27230059993...

i want to compare values of node_source and node_destionation between row 1 and row 2, row 3 and row 4.

if node_source in row 1 == node_destination in row 2 AND node_destination in row 1 == node_source in row 2 THEN display only first row (id=1)

if node_source in row 3 == node_destination in row 4 AND node_destination in row 3 == node_source in row 4 THEN display only third row (id=3)

The final output like this :

id | node_source    | node_destination | path
1  |        1       |       0          | {"coordinates": [[-6.27400693507...
3  |        1       |       2          | {"coordinates": [[-6.24568104953...

Here my code (but not works) :

SELECT * FROM graph t1 
    WHERE NOT EXISTS(SELECT * FROM graph t2 
                        WHERE t2.node_source = t1.node_destination AND t2.node_destination = t1.node_source
                    )

Help. Thanks.

Gordon Linoff · Accepted Answer · 2017-02-19 11:59:48Z

1

I think you want this:

SELECT g.*
FROM graph g
WHERE g.node_source <= g.node_destination
UNION ALL
SELECT g.*
FROM graph g
WHERE g.node_source > g.node_destination AND
      NOT EXISTS (SELECT 1
                  FROM graph g2
                  WHERE g2.node_source = g.node_destination AND g2.node_destination = g.node_source
                 );

This will select one edge of each pair.

answered Feb 19, 2017 at 11:59

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user3600910 · Accepted Answer · 2017-02-19 12:13:05Z

0

you can use self join and compare between the id and id+1,

but notice that this will also compare between 2 and 3 and so on

select a.id,
       a.node_source,
       a.node_destination,
       a.path
from   graph a
inner join graph b on a.id=b.id+1
where (a.node_source<> b.node_destination
or   b.node_source<> a.node_destination)

answered Feb 19, 2017 at 12:13

user3600910

3,1594 gold badges24 silver badges36 bronze badges

2 Comments

ahm Over a year ago

but, if i change second row with : id | node_source | node_destination| 2 | 4 | 1 | the output will be different :

id | node_source  | node_destination | path  2  |        4     |       1          | {"coordinates": [[-6.24568104953...  3  |        1     |       2          | {"coordinates": [[-6.24568104953...

user3600910 Over a year ago

as far as i understood in that case you should get rows 1,2 and 3 ,and that's what the query will bring

Collectives™ on Stack Overflow

MYSQL - WHERE values in two columns

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related