Multiple session table joins

[p175]

Re: Multiple session table joins

No takers ?

[p175]

Re: Multiple session table joins

Bump, please ?

[Tonkuma]

Re: Multiple session table joins

I feel hard to understand the tables structure and your required
result, mainly my poor English capabilities.
Especially, this part is difficult for me.[color=blue]
> and T4 has more than any other table at 4,[/color]

It is difficult to explain by English my understandings. . So, I'll show
you by Example. It may include my misunderstandin gs, please don't
hesitate point out them..

By the way, I have some questions
1) How to math value of T4 with another tables data.
2) Are there any meaning in that the value of T3 are descending.

If you give us an example including more various cases. It will help
greatly us to understand the problem.

------------------------ Commands Entered -------------------------
SELECT * FROM P175.T1;
-------------------------------------------------------------------

ID VALUE
----------- -----
1 A
1 B
2 A
3 A
3 B
3 C

6 record(s) selected.


------------------------ Commands Entered -------------------------
SELECT * FROM P175.T2;
-------------------------------------------------------------------

ID VALUE
----------- -----
3 a
3 b

2 record(s) selected.


------------------------ Commands Entered -------------------------
SELECT * FROM P175.T3;
-------------------------------------------------------------------

ID VALUE
----------- -----
1 Z
1 1
3 Z
3 Y
3 X

5 record(s) selected.


------------------------ Commands Entered -------------------------
SELECT * FROM P175.T41;
-------------------------------------------------------------------

ID VALUE
----------- -----
1 G

1 record(s) selected.


------------------------ Commands Entered -------------------------
SELECT * FROM P175.T42;
-------------------------------------------------------------------

ID VALUE
----------- -----
1 I

1 record(s) selected.


------------------------ Commands Entered -------------------------
SELECT * FROM P175.T43;
-------------------------------------------------------------------

ID VALUE
----------- -----
1 K

1 record(s) selected.


------------------------ Commands Entered -------------------------
SELECT * FROM P175.T44;
-------------------------------------------------------------------

ID VALUE
----------- -----
1 H

1 record(s) selected.



------------------------ Commands Entered -------------------------
SELECT
COALESCE(t1.ID, t2.ID, t3.ID, t4.ID) AS ID
, t1.value AS T1
, t2.value AS T2
, t3.value AS T3
, value41||value4 2||value43||val ue44 AS T4
FROM (SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM P175.T1
) AS t1
FULL OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM P175.T2
) AS t2
ON t2.ID = t1.ID
AND t2.rn = t1.rn
FULL OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value DESC) rn
FROM P175.T3
) AS t3
ON t3.ID = COALESCE(t1.ID, t2.ID)
AND t3.rn = COALESCE(t1.rn, t2.rn)
FULL OUTER JOIN
(SELECT COALESCE(t41.ID , t42.ID, t43.ID, t44.ID)
, COALESCE(t41.va lue, ' ')
, COALESCE(t42.va lue, ' ')
, COALESCE(t43.va lue, ' ')
, COALESCE(t44.va lue, ' ')
, ROWNUMBER() OVER(ORDER BY
COALESCE(t41.va lue, ' ')
||COALESCE(t42. value, ' ')
||COALESCE(t44. value, ' ')
||COALESCE(t43. value, ' ')
DESC
)
FROM (SELECT ID, Value
, 1 rn
FROM P175.T41
) t41
FULL OUTER JOIN
(SELECT ID, Value
, 2 rn
FROM P175.T42
) t42
ON t42.ID = t41.ID
AND t42.rn = t41.rn
FULL OUTER JOIN
(SELECT ID, Value
, 3 rn
FROM P175.T43
) t43
ON t43.ID = COALESCE(t41.ID , t42.ID)
AND t43.rn = COALESCE(t41.rn , t42.rn)
FULL OUTER JOIN
(SELECT ID, Value
, 4 rn
FROM P175.T44
) t44
ON t44.ID = COALESCE(t41.ID , t42.ID, t43.ID)
AND t44.rn = COALESCE(t41.rn , t42.rn, t43.rn)

) AS t4 (ID, value41, value42, value43, value44, rn)
ON t4.ID = COALESCE(t1.ID, t2.ID, t3.ID)
AND t4.rn = COALESCE(t1.rn, t2.rn, t3.rn)
ORDER BY
ID, COALESCE(t1.rn, t2.rn, t3.rn, t4.rn)
;
-------------------------------------------------------------------

ID T1 T2 T3 T4
----------- -- -- -- ----
1 A - Z G
1 B - 1 I
1 - - - H
1 - - - K
2 A - - -
3 A a Z -
3 B b Y -
3 C - X -

8 record(s) selected.

[p175]

Re: Multiple session table joins

Tonkuma,

Thanks so much for replying, greatly appreciated. So, we were right
with your input data up to table 3. There is no table 41, 42 .. 43 etc,
it will simply increment to 4, 5, 6 etc and will not be combined into
one column. Each table should be represented by it's own distinct
column. In your example, tables 41, 42, 43 etc would appear in seperate
columns, not combined in column T4. Tables T4 and T5 for example might
look like so:

select * from T4 where ID = 1
ID VALUE
1 G
1 I
1 H
1 K

select * from T5 where ID = 1
ID VALUE
1 A
1 L
1 M
1 N
1 O
1 P
1 Q
1 R

The final output would then have to look like:
ID T1 T2 T3 T4 T5
----------------------------------
1 A - Z G L
1 B - 1 I M
1 - - - H N
1 - - - K O
1 - - - - P
1 - - - - Q
1 - - - - R
1 - - - - A
2 A - - - -
3 A a Z -
3 B b Y -
3 C - X -

Each table MAY therefore contain a different number of records per ID.
There is a base table that will contain a single row containing the
distinct IDs in case T1 contains no records for ID 1. I am not
interested in results where there are no records in ANY of the tables
T1- T5 for ID 1.

Select * from BASE
ID
--
1
2
3

To answer your questions, 1) see above as we do not combine multiple
tables into a single column. 2) There was no meaning in the order my
example appeared. Ideally I guess each column should be ordered by ASC
if possible, if too difficult or expensive then it is not essential,
but preferrable.

Just to confirm, all columns are integer values. I was only using
alphas as examples. My mistake, sorry.

Again thanks so much for the help.

Tim

[Tonkuma]

Re: Multiple session table joins

If you don't like to use FULL OUTER JOIN, this may be one way:

SELECT
b.ID
, MIN(t1.value) AS T1
, MIN(t2.value) AS T2
, MIN(t3.value) AS T3
, MIN(t4.value) AS T4
, MIN(t5.value) AS T5
FROM BASE b
LEFT OUTER JOIN
(VALUES 1, 2, 3, 4, 5) AS T(n)
ON 0=0
LEFT OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM T1
) AS t1
ON t1.ID = b.ID AND n = 1
LEFT OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM T2
) AS t2
ON t2.ID = b.ID AND n = 2
LEFT OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM T3
) AS t3
ON t3.ID = b.ID AND n = 3
LEFT OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM T4
) AS t4
ON t4.ID = b.ID AND n = 4
LEFT OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM T5
) AS t5
ON t5.ID = b.ID AND n = 5
WHERE COALESCE(t1.rn, t2.rn, t3.rn, t4.rn, t5.rn) IS NOT NULL
GROUP BY
b.ID, COALESCE(t1.rn, t2.rn, t3.rn, t4.rn, t5.rn)
ORDER BY
b.ID, COALESCE(t1.rn, t2.rn, t3.rn, t4.rn, t5.rn)
;
--------------------------------------------------------------------

ID T1 T2 T3 T4 T5
-- -- -- -- -- --
1 A - 1 G A
1 B - Z H L
1 - - - I M
1 - - - K N
1 - - - - O
1 - - - - P
1 - - - - Q
1 - - - - R
2 A - - - -
3 A a X - -
3 B b Y - -
3 C - Z - -

12 record(s) selected.


But, I like to use FULL OUTER JOIN
(More simple, easy to understand, less tricky coding)
Example:

SELECT
b.ID
, t1.value AS T1
, t2.value AS T2
, t3.value AS T3
, t4.value AS T4
, t5.value AS T5
FROM BASE b
LEFT OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM T1
) AS t1
FULL OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM T2
) AS t2
ON t2.ID = t1.ID
AND t2.rn = t1.rn
FULL OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM T3
) AS t3
ON t3.ID = COALESCE(t1.ID, t2.ID)
AND t3.rn = COALESCE(t1.rn, t2.rn)
FULL OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM T4
) AS t4
ON t4.ID = COALESCE(t1.ID, t2.ID, t3.ID)
AND t4.rn = COALESCE(t1.rn, t2.rn, t3.rn)
FULL OUTER JOIN
(SELECT ID, Value
, ROWNUMBER() OVER(PARTITION BY ID
ORDER BY value) rn
FROM T5
) AS t5
ON t5.ID = COALESCE(t1.ID, t2.ID, t3.ID, t4.ID)
AND t5.rn = COALESCE(t1.rn, t2.rn, t3.rn, t4.rn)
ON b.ID = COALESCE(t1.ID, t2.ID, t3.ID, t4.ID, t5.ID)
ORDER BY
b.ID, COALESCE(t1.rn, t2.rn, t3.rn, t4.rn, t5.rn)
;
--------------------------------------------------------------------

ID T1 T2 T3 T4 T5
-- -- -- -- -- --
1 A - 1 G A
1 B - Z H L
1 - - - I M
1 - - - K N
1 - - - - O
1 - - - - P
1 - - - - Q
1 - - - - R
2 A - - - -
3 A a X - -
3 B b Y - -
3 C - Z - -

12 record(s) selected.

[p175]

Re: Multiple session table joins

Tonkuma, I don't know how to thank you.

It works brilliantly.

Tim