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 million 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?

On Sat, 20 Oct 2007 05:38:22 -0000, Legend
<rahul986@gmail.com> wrote:

>I was wondering if it is possible to group
>linked elements using SQL directly.

A lot is possible with SQL.
It's not clear what you mean with 'linked'.

>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


My IQ is limited, I can't discover a reason why the given
input would lead to the requested output.
Are you sure your sample data is exact?
Can you elaborate on what you are trying to achieve?

>or maybe something like with the above one still preferred:
>1,2,3,4,9
>5,6
>7,8
>extracted into another table...

It can be done, but why store in another table?

>One other question I had was, can a database have a million tables?

In general, even if physically possible, that's a very bad
idea, and a sure sign of a flaw in the database design.

>I mean, if I had many such groups, would it be efficient
>to create a table for each group

No, not at all.

>or is there any other approach?

Only store primary data in the database,
write views to look at the data in any way you want.
--
( Kees
)
c[_] Famous last words - Don't worry, I can handle it. (#441)

On 20 Oct, 15:10, Kees Nuyt <k.n...@nospam.demon.nl> wrote:
> On Sat, 20 Oct 2007 05:38:22 -0000, Legend
>
> <rahul...@gmail.com> wrote:
> >I was wondering if it is possible to group
> >linked elements using SQL directly.
>
> A lot is possible with SQL.
> It's not clear what you mean with 'linked'.
>
>
>
> >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
>
> My IQ is limited, I can't discover a reason why the given
> input would lead to the requested output.
> Are you sure your sample data is exact?
> Can you elaborate on what you are trying to achieve?
>
> >or maybe something like with the above one still preferred:
> >1,2,3,4,9
> >5,6
> >7,8
> >extracted into another table...
>
> It can be done, but why store in another table?
>
> >One other question I had was, can a database have a million tables?
>
> In general, even if physically possible, that's a very bad
> idea, and a sure sign of a flaw in the database design.
>
> >I mean, if I had many such groups, would it be efficient
> >to create a table for each group
>
> No, not at all.
>
> >or is there any other approach?
>
> Only store primary data in the database,
> write views to look at the data in any way you want.
> --
> ( Kees
> )
> c[_] Famous last words - Don't worry, I can handle it. (#441)

This question was also posted in mysql.com's own forums.

I understand the relationship between the dataset and the result set:
1 is linked to 4 via 2 & 3.
5 & 6 are linked directly, as are 7 & 8, and 1 & 9

So the OP wants a result that looks like:
1,2,3,4,9
5,6
7,8

This requires recursion, which can be achieved in a variety of ways,
including:

1. LEFT JOIN the table to itself as often as you think necessary to
traverse the tree.
2. Use a Stored Procedure
3. Use a nested set model instead of an adjacency list
4(my personal favourite). Use an external language (like php) to
handle the recursion
5. A combination of some of the above.

On Sat, 20 Oct 2007 14:51:23 -0000, strawberry
<zac.carey@gmail.com> wrote:

>This question was also posted in mysql.com's own forums.
>
>I understand the relationship between the dataset and the result set:
>1 is linked to 4 via 2 & 3.
>5 & 6 are linked directly, as are 7 & 8, and 1 & 9

Aha, now i see. Thanks for the clarification.
--
( Kees
)
c[_] I don't want to be your other half.
I believe that One and One make TWO.
(Alanis Morrisette: "Not the Doctor") (#185)