CODEWITHSUNDEEP
mysqldatabasegraphunion
lollercoaster
lollercoaster

Reputation: 16523

MySQL vertices & edges intersection

I have 2 tables: edges and users

edges (with constraint: id1 < id2):

id1
name1
id2
name2

users:

id
name

I'd like to get a result set of only edges (id1, name1, id2, name2) such that BOTH id1 and id2 are in the users table. This seems pretty simple, but I'm having trouble getting 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?

Upvotes: 3

Views: 233

Answers (3)

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

Give this a 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

Upvotes: 1

Arion
Arion

Reputation: 31249

Maybe 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
  )

Upvotes: 0

jordeu
jordeu

Reputation: 6821

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

Upvotes: 1

Related Questions