Grouping Elements

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Grouping Elements

Post 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?
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post 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.
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post 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...
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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!
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post 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...
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

I'm not finding and efficient way of doing this... Can someone advice please?
Post Reply