Clever Geek Handbook

MySQL vertices and edge intersections

I have 2 tables: edges

andusers

edges

(with limitation: id1 <id2):

id1
name1
id2
name2

users

:

id
name

I would like to get a result set of only edges (id1, name1, id2, name2) so that BOTH id1 and id2 are in the users table. It seems pretty simple, but I find it hard to get it. My attempt:

SELECT
   e.id1 AS id1,
   e.name1 AS name1,
   e.id2 AS id2,
   e.name2 AS name2
FROM 
   edges AS e,
   users AS u
WHERE u.id = e.id1

UNION

SELECT
   e.id1 AS id1,
   e.name1 AS name1,
   e.id2 AS id2,
   e.name2 AS name2
FROM 
   edges AS e,
   users AS u
WHERE u.id = e.id2

Any pointers?

+3


source to share


3 answers


Try using two INNER JOINS like this:



SELECT * FROM edges e 
JOIN users u1 ON e.name1 = u1.name 
JOIN users u2 ON e.name2 = u2.name
+1


source


Try:



select e.id1, e.name1, e.id2, e.name2 from edges e
join users u1 on e.id1 = u1.id
join users u2 on e.id2 = u2.id
+1


source


Perhaps something like this:

SELECT
   e.id1 AS id1,
   e.name1 AS name1,
   e.id2 AS id2,
   e.name2 AS name2
FROM 
   edges AS e
WHERE EXISTS
  (
      SELECT
        NULL
      FROM
        users AS u
      WHERE
        u.id = e.id1
        AND u.id = e.id2
  )
0


source






More articles:


All Articles