Search code examples
mysqlsqljoinnodesedges

SELECT depending on value from second table


I am using Gephi, connected to a MySQL database. There are two tables:

Nodes 

**id | label**
----------------
   1 | a 
   2 | b
   3 | c
   4 | d

Edges 

**source | target**
----------------
       4 | 3
       1 | 2
       2 | 3
       3 | 1
       2 | 1
       2 | 4

Values for source and target (Edges table) should correspond to id (Nodes table). I want one query to:

  1. Select from Nodes table where label is b.
  2. id of b is 2, so select these values from Edges table where source is 2.
  3. Now target value is 3, 1, 4 where source is 2, so select these values from Nodes table where id is 3, 1, 4.

I tried this query, but it's not giving correct results:

SELECT id, label FROM nodes WHERE id IN(
   SELECT target FROM edges WHERE target In(
     SELECT id FROM nodes WHERE label = 'b'
   )
)

Result should look like this:

**id | label**
---------------- 
   2 | b
   3 | c
   1 | a
   4 | d

Solution

  • Just the join the nodes table twice with edges table like this:

    SELECT distinct n2.*
    FROM nodes n1
    JOIN edges e on n1.id = e.source
    JOIN nodes n2 on n2.id in (e.source, e.target)
    WHERE n1.label = 'b'
    

    Demo @ SQLFiddle