make a matrix in a listform

Question

I have a table with users in mysql. users:

+---+-------+
| id| name  |
|---+-------+
| 1 | john  |
| 2 | david | 
| 3 | paul  |
+---+-------+

What I want is a table with relations of all users like this (it's like a matrix in a listform):

1|2 
1|3
2|1
2|3
3|1
3|2

Which query will result in the desired table? an how can i keep it updated when i will add a user?

Many thanks in advance!

ps. Eventually i want to use it to store the degree of separation between users with the help of a relations table Table with 3 levels of degree of separation. I'm not really sure if this is a step in the right direction...

score 0 · Accepted Answer · edited May 28 '14 at 19:50

If you want all combinations of users, a cross join will do the trick. In this case you're cross-joining the table against itself. The `WHERE' clause makes sure you don't get the combinations (1,1), (2,2), and (3,3):

SELECT a.id, b.id
FROM myusers a
CROSS JOIN myusers b
WHERE a.id <> b.id
ORDER BY a.id, b.id;

A table won't update when you add a user. I'd recommend a view instead, as it will always be up to date. If you're unfamiliar with views, check here for starters.

The view syntax is:

CREATE OR REPLACE VIEW usercombos (id1 INT, id2 INT) AS
SELECT a.id as id1, b.id as id2
FROM myusers a
CROSS JOIN myusers b
WHERE a.id <> b.id
ORDER BY a.id, b.id;

You can select from a view just like it was a table:

SELECT * FROM usercombos;

make a matrix in a listform

1 Answers1