1

I have an ordinary user table:

Users: ID-NAME

Then I have a Connections-table:

Connections: ID-MASTER_ID-SLAVE_ID

Here are some users:

INSERT INTO Users VALUES(1, 'Mark');
INSERT INTO Users VALUES(2, 'Jack');
INSERT INTO Users VALUES(3, 'John');
INSERT INTO Users VALUES(4, 'Steve');

Here are some connections:

INSERT INTO Connections VALUES(null, 1, 3);
INSERT INTO Connections VALUES(null, 1, 4);
INSERT INTO Connections VALUES(null, 2, 3);

Now, if I want to see the list of connections, I would have:

Connections for Master Mark: John and Steve
Connections for Master Jack: John

If I need to get, grouped, all the connections for John and all the user in that group, I can see that I would need to fetch two groups:

Mark's group and Jack's group.

This is because John is slave for both.

Of course I could do a while-statement. But as Im using asynchronous programming with node.js it becomes difficult. The best would be to get all the groups that John belongs to AND also the users in that group. So The result would be two rows:

ROW1: Group owned by Mark With users: "John" and "Steve"
ROW2: Group owned by Jack With users: "John"

Is there an easy way to do it with a single query or do I need to create a stored procedure?

oderfla
  • 1,695
  • 4
  • 24
  • 49
  • Possible duplicate of [PostgreSQL recursive with](http://stackoverflow.com/questions/3307480/postgresql-recursive-with) – Jakub Kania Jan 20 '17 at 10:16
  • This looks like what should help you: http://stackoverflow.com/questions/39805736/get-join-table-as-array-of-results-with-postgresql-nodejs/ – vitaly-t Jan 20 '17 at 10:46

0 Answers0