Re: Joining Fields

On Wed, 29 Nov 2000 16:25:57 GMT, sw_at_weinerfamily.org wrote:

>This may be interesting:
>
>I have a table that looks like this
>
>Table A
>TYPE CODE
>--------------
>P 1
>P 2
>P 3
>D 1
>D 2
>
>I have another table that looks like this:
>
>Table B
>ID PCODE DCODE MASTER
>----------------------------
>1 1 1 AAA
>2 1 1 XXX
>3 1 5 AAA
>4 2 1 XXX
>5 3 2 AAA
>6 1 1 AAA
>
>What I need to do is iterate through Table A and create combinations
>such as:
>P1P2
>P1P3
>P1D1
>P1D2
>P2P1
>P2P3
>...

Just quickly on this first part, to create the combined codes, just join the table to itself with no real join information.

SELECT
   a1.Type || a1.Code || a2.Type || a2.Code FROM
     a a1, a a2,
WHERE NOT a1.rowid = a2.rowid;

>
>Then I need to count how many unique MASTERS in Table B have each
>particular combination
>
>So the result might be:
>
>CODE MASTER_COUNT
>-------------------------
>P1P2 0
>P1P3 2
>P1D1 2
>P1D2 0
>P2P1 1
>P2P3 0
>...
>
>
>I realize this is probably multiple queries but what is the best way to
>do this?
>
>Thank you!!!!
>
>
Received on Wed Nov 29 2000 - 18:09:44 CET