How to combine separate columns from different tables in a single table

Question

I have the following VFP cursors (tables):

and i need this result:

I've tried FULL JOIN, CROSS JOIN, UNION ALL, etc. But i can't solve the problem.

Please give me any help.

I'm using Visual FoxPro 9.0

Here's the code:

lkey = SYS(2015) 
CREATE CURSOR cur1 (unico c(10),valor c(2))
INSERT INTO cur1 values(lkey, 'A1')
INSERT INTO cur1 values(lkey, 'A2')
INSERT INTO cur1 values(lkey, 'A3')

CREATE CURSOR cur2 (unico c(10),valor c(2))
INSERT INTO cur2 values(lkey, 'B1')
INSERT INTO cur2 values(lkey, 'B2')
INSERT INTO cur2 values(lkey, 'B3')    

INSERT INTO cur3 values(lkey, 'C1')
INSERT INTO cur3 values(lkey, 'C2')
INSERT INTO cur3 values(lkey, 'C3')

Thanks!

unico is a key to join on? Or does only the position in the cursors matter to determine which rows should be joined? — sticky bit
I don't really need it. I put that column for matching records only, is that ok? — Irwin

sticky bit sticky bit · Accepted Answer · 2019-03-29T20:38:11

If only the position of a record in the cursor makes up the join criteria you can use recno():

SELECT cur1.valor, ;
       cur2.valor, ;
       cur3.valor ;
       FROM (SELECT valor, ;
                    RECNO() rn ;
                    FROM cur1) cur1 ;
            FULL JOIN (SELECT valor, ;
                              RECNO() rn ;
                              FROM cur2) cur2 ;
                      ON cur2.rn = cur1.rn ;
            FULL JOIN (SELECT valor, ;
                              RECNO() rn ;
                              FROM cur3) cur3 ;
                      ON cur3.rn = cur2.rn

How to combine separate columns from different tables in a single table

1 Answers