Page 1 of 1

Grouping Elements

Posted: Sat Oct 20, 2007 1:34 am
by legend986
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?

Posted: Sat Oct 20, 2007 9:35 am
by aaronhall
I'm having trouble seeing the pattern... it might help if we knew what the column values represented and what their relationships were.

No limit on the number of tables that I know of (aside from memory), though by default, MyISAM tables have a 4gb data limit.

Posted: Sat Oct 20, 2007 11:01 am
by legend986
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...

Posted: Sat Oct 20, 2007 1:13 pm
by califdon
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!

Posted: Sat Oct 20, 2007 1:15 pm
by legend986
No... This is the way I was able to collect the data from an analysis... Its not the fault of the database (I hope)... Its like I need to get some result from a database which is stored in this format...

Posted: Mon Oct 22, 2007 9:52 am
by legend986
I'm not finding and efficient way of doing this... Can someone advice please?