Self join one table

[banem]

Hello,

It is a bit tricky to explain, but I will try with sample. I have one table:

ID, server
1, server1
2, server2
3, server1
4, server3
5, server4

I need to fill second table with records like this:

IDFrom, IDTo, Server1, Server2
1, 2, server1, server2
2, 5, server2, server5
3, 4, server1, server4
4, 3, server3, server1
5, 1, server4, server1

Goal is to create in first column same ID's as in first table and same server as in first table. Second ID is created using random order of ID's from same table, but taking in care that servers cannot be equal. Example of wrong records are:

1, 1, server1, server1 (same servers)
1, 3, server1, server1 (different ID's but same servers again)

I am struggling with this for over 2 weeks and I have no acceptable solution.

Actually I have make this work by creating one temp table which contains all possible ID to ID records and then using additional query I can extract desired records. Problem in this case is that when table have 10.000 records. In this case temp table will contain 10.000x10.000 records = 100 M of records which is too much. As I plan to run this on as many as 1 M records in first table, it looks like impossible task.

Is this possible with SQL?

Thanks!

[deepuv04]

Hello,

It is a bit tricky to explain, but I will try with sample. I have one table:

ID, server
1, server1
2, server2
3, server1
4, server3
5, server4

I need to fill second table with records like this:

IDFrom, IDTo, Server1, Server2
1, 2, server1, server2
2, 5, server2, server5
3, 4, server1, server4
4, 3, server3, server1
5, 1, server4, server1

Goal is to create in first column same ID's as in first table and same server as in first table. Second ID is created using random order of ID's from same table, but taking in care that servers cannot be equal. Example of wrong records are:

1, 1, server1, server1 (same servers)
1, 3, server1, server1 (different ID's but same servers again)

I am struggling with this for over 2 weeks and I have no acceptable solution.

Actually I have make this work by creating one temp table which contains all possible ID to ID records and then using additional query I can extract desired records. Problem in this case is that when table have 10.000 records. In this case temp table will contain 10.000x10.000 records = 100 M of records which is too much. As I plan to run this on as many as 1 M records in first table, it looks like impossible task.

Is this possible with SQL?

Thanks!

try the following query


SELECT S.ID,S.SERVER,t.id,T.SERV
FROM TABLE1 S, (SELECT ID,SERVER FROM TABLE1) AS T
WHERE T.SERVER <> S.SERVER
ORDER BY S.ID


IF the above query is returning the values you want then use insert statement along with that

[banem]

try the following query


SELECT S.ID,S.SERVER,t.id,T.SERV
FROM TABLE1 S, (SELECT ID,SERVER FROM TABLE1) AS T
WHERE T.SERVER <> S.SERVER
ORDER BY S.ID


IF the above query is returning the values you want then use insert statement along with that

Thanks, but what about random order?