Forming Groups which are circular in nature

Database
Enthusiast

Forming Groups which are circular in nature

Hi All,

I have data as below from which I should form the groups:

Input:-

Table1:-

Parent_Char - Child_Char

A - B

X - Y

B - C

Y - X

C - A

M - N

N - M

Table2:-

Char - Value

A - 100

X - 600

Y - 40

N - 500

M - 100

B - 60

C - 50

Z - 40

I should form the groups using Table 1 and decide Group ID using Table 2.

The logic should be as below:-

Start from Parent_Char (i.e. A) and get the Child_Char (i.e. B), use this Child_Char as Parent_Char to get next Child_Char (i.e. C), use this Child_Char as Parent_Char to get the next Child_Char (i.e. A) and so on.

Repeat this process till we arrive the starting Parent (i.e. A).

Hence, the groups will be as below:-

A ---> B ---> C ---> A

X ---> Y ---> X

M ---> N ---> M

Take the highest value in Table 2 as Group ID.

Expected Output is :-

Group ID - Member

A - A

A - B

A - C

X - X

X - Y

N - N

N - M

Please advise.

Thanks a lot in advance.

Sagar

3 REPLIES
Junior Contributor

Re: Forming Groups which are circular in nature

Hi Sagar,

how do you know that A is the starting point and not B or C?

A ---> B ---> C ---> A

B ---> C ---> A ---> B

C ---> A ---> B ---> C

Dieter

Enthusiast

Re: Forming Groups which are circular in nature

Hi Dieter,

Based on some business conditions the starting point can be decided.

In this case, assuming starting point as A to check further.

Sagar.

Junior Contributor

Re: Forming Groups which are circular in nature

Hi Sagar,

for me assumimg is easy, but it's hard for SQL :-)

There must be a SQL-condition to find the starting point.

Dieter