Combining 2 tables as per a 3rd TEMPLATE table 1

[VickyC]

Hi to all. I am asking for help with a problem where I use the data in 3 tables to fill a 4th table. This is part of a larger problem, but I tried to represent my difficulty as clearly as I can.

Start with two tables, tbl_Pos and tbl_Neg.

 [B]
tbl_Pos                     tbl_Neg

Val_P                       Val_N [/B]
  1                           -1
  2                           -2
  4                           -4
  8                           -8
 16                          -16

From each table above, I created a normalized table holding all permutations of Val_P and of Val_N.
These are shown below, to the Left.

 [B]

tbl_Perms_P                    tbl_Perms_N                     tbl_TEMPLATE [b](will ALWAYS have five 1's representing five Val_P values,[/b][u][/u] 
                                                                                          [b]and five 0's,representing five Val_N values)[/b][u][/u]  
ID_P   Col_P   Val_P           ID_N   Col_N   Val_N            Col_T     Val_T[/B]
  1      1       1               1       1      -1               1       [b]1[/b]
  1      2       2               1       2      -2               2       [b]1[/b]
  1      3       4               1       3      -4               3       [b]1[/b]
  1      4       8               1       4      -8               4       [b]0[/b]
  1      5      16               1       5     -16               5       [b]0[/b]
                                                                 6       [b]1[/b]
  2      1       1               2       1      -1               7       [b]1[/b]    
  2      2       2               2       2      -2               8       [b]0[/b]
  2      3       4               2       3      -4               9       [b]0[/b]
  2      4      16               2       4     -16              10       [b]0[/b]
  2      5       8               2       5      -8
 
  3   etc.....
...

120      1      16             120       1     -16
120      2       8             120       2      -8
120      3       4             120       3      -4
120      4       2             120       4      -2
120      5       1             120       5      -1

So far, so good. Now comes the part that is beyond me! I want to COMBINE each ID_P with each ID_N according to a template shown in tbl_TEMPLATE above at right.

Let's say we are combining ID_P=1 with ID_N = 2. Here is how to interpret the Val_T values in tbl_TEMPLATE. They are instructing us to do....

Take the FIRST THREE Val_P values from ID_P=1 in order, then...
take the FIRST TWO Val_N values from ID_N = 2 in order, then...
take the NEXT TWO Val_P values from ID_P=1 in order, then...
take the NEXT THREE Val_N values from ID_N=2 in order.

The result when combining ID_P=1 with ID_N=2 using the template should look like ID_C = 2 below....

[b]
tbl_Perms_Combined

ID_C      Col_C      Val_C   [/b]
  1         1          1         
  1         2          2
  1         3          4
  1         4         -1
  1         5         -2
  1         6          8
  1         7         16
  1         8         -4
  1         9         -8
  1        10        -16

  2         1          1      
  2         2          2
  2         3          4
  2         4         -1
  2         5         -2
  2         6          8
  2         7         16
  2         8         -4
  2         9        -16
  2        10         -8

.....
14400       1         16
14400       1          8
14400       1          4
14400       1        -16
14400       1         -8
14400       1          2
14400       1          1
14400       1         -4
14400       1         -2
14400       1         -1

So, starting with tbl_Perms_P and tbl_Perms_N and tbl_TEMPLATE, can anyone help me with code to build tbl_Perms_Combined ?


Much thanks in advance
Vicky C.

 

[PHV]

This should work (typed, untested):

SELECT DISTINCT ID_N+120*(ID_P-1) AS ID_C,Col_T AS Col_C,IIf(Val_T=1,Val_P,Val_N) AS Val_C
FROM tbl_Perms_P P,tbl_Perms_N N
,(SELECT Col_T,Val_T,(SELECT Count(*) FROM tbl_TEMPLATE WHERE Col_T<=C.Col_T AND Val_T=C.Val_T) AS Col_PN FROM tbl_TEMPLATE C) T
WHERE (Col_P=Col_PN AND Val_T=1) OR (Col_N=Col_PN AND Val_T=0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

Wonderful solution! I can say for sure that I wouldn't have ever thought of this approach.

The code works perfectly, but it is somewhat slow. In my actual implementation, there are 7Ns and 7Ps, not 5 as in my posted example. But, I should be able to use your ideas to save intermediate results in tables to speed things up.

I'm still a bit baffled by something you did that, still, seems to work fine...
In the 'INNERMOST' SELECT/FROM/WHERE, you use code like "Col_T< = C.Col_T". But, alias C is defined in the SELECT/FROM code that CONTAINS this innermost code. That puzzles me because I would think that the innermost SELECT/FROM/WHERE would have to be processed first??

SELECT DISTINCT 
	 ID_N+120*(ID_P - 1) AS ID_C, Col_T AS Col_C, IIf(Val_T = 1, Val_P, Val_N) AS Val_C
FROM 
	 tbl_Perms_P_NORM P, tbl_Perms_N_NORM N,
           (
	    SELECT Col_T, Val_T,
	        (
		    SELECT 	Count(*) 
		    FROM tbl_TEMPLATE 
		    WHERE (Col_T <= C.Col_T) AND (Val_T  = C.Val_T)
		) AS Col_PN 

	    FROM tbl_TEMPLATE C
	   ) AS T

WHERE 
	(Col_P = Col_PN AND Val_T = 1) OR (Col_N = Col_PN AND Val_T = 0);

Again, thanks for some really insightful code!
Vicky C.