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
lollercoaster
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
jordeu
source
to share
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
Mosty mostacho
source
to share
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
Arion
source
to share