I was wondering if it is possible to group linked elements using SQL
directly. For example, if I have some table like:
Column A | Column B | Column C | Column D
1 | 2 | A11 | A32
2 | 3 | A13 | A22
3 | 4 | A14 | A23
5 | 6 | A19 | A24
7 | 8 | A18 | A25
1 | 9 | A17 | A26
I want to group all linked items together. So in the above case my
output would look something like:
Table 1:
1
2
3
4
9
Table 2:
5
6
Table 3:
7
8
or maybe something like with the above one still preferred:
1,2,3,4,9
5,6
7,8
extracted into another table...
One other question I had was, can a database have a thousand tables? I
mean, if I had many such groups, would it be efficient to create a
table for each group or is there any other approach?
Grouping Elements
Moderator: General Moderators
Thank you for replying... As such the numbers represent some strings to be more precise and this relationship is stored in a different table that I can use if I want to display the actual data...
Column A | Column B | Column C | Column D
1 | 2 | A11 | A32
2 | 3 | A13 | A22
3 | 4 | A14 | A23
5 | 6 | A19 | A24
7 | 8 | A18 | A25
1 | 9 | A17 | A26
From the above table, 1 is related to 2 and 2 is related to 3 and 3 to 4 and later we see that 1 is related to 9 also so we group all of these together... Now 5,6 and 7,8 are not related to each groupwise so we allot them different groups... I hope I was able to convey my idea...
Column A | Column B | Column C | Column D
1 | 2 | A11 | A32
2 | 3 | A13 | A22
3 | 4 | A14 | A23
5 | 6 | A19 | A24
7 | 8 | A18 | A25
1 | 9 | A17 | A26
From the above table, 1 is related to 2 and 2 is related to 3 and 3 to 4 and later we see that 1 is related to 9 also so we group all of these together... Now 5,6 and 7,8 are not related to each groupwise so we allot them different groups... I hope I was able to convey my idea...
While it might be possible to perform some kind of grouping like you describe, it's a way bad idea. Relational databases are designed to STORE the data in relational structures. Whenever you find the need to do something like this, it is a sign that the underlying table structure is faulty. While it is possible to drive a nail using a wrench turned on its side, it is simply the wrong tool!