Merging two table's fields 1

[linousa]

I have a question - I have 2 tables with the different fields and one unique key field(id) in both those tables and I need to merge all those 2 table's fields based on that id key field, number of records(rows) should stay the same, only the number of fields(columns) should change.
Forgot to tell, these tables have >1M records.

Thank you in advance!

 

[Olaf Doschke]

Hi,

left join them

Select * from table1 t1 left join table2 t2 on t1.id=t2.id

You only keep record numbers, if ids are unique in both table1 and table2, 1:1 related tables.

Otherwise, you want to do something as pivoting of data and that needs more information from your side. There is no general "all fields" joining of 1:n many records so all data is joined in one row and the largest n of some id determines the maximum column count.

To get that layout visually all you need to do is SELECT * FROM table1 and SELECT * from table2 into two separate result sets and then simply iterate all rows of table2 for each row of table1 and fill in cells, eg in an excel sheet, as you which. you wouldn't already do that in SQL.

Bye, Olaf.

 

[linousa]

There is a lot of fields in both tables >255, how do we join them if we want to join only 1,2,3 particular fields from the second table?

What we are trying to accomplish is that we need to get values from second table(from 2 fields at this point) and we have one key field, 1:1, first table is longer though. Any ideas?

 

[Olaf Doschke]

What I suggested several times already: SET RELATION. Here in conjunction with SET FIELDS, but you can't have more than 255 fields overall.

USE table1
USE table2
SELECT table2
SET ORDER TO TAG idtag
SELECT table1
SET RELATION TO id INTO table2

*Now tables are related you can for example 
SET FIELDS GLOBAL
SELECT table1
SET FIELDS TO ALL && including all table1 fields
SELECT table2 
SET FIELDS TO fieldx,fieldy,fieldz
BROWSE

But this only works if the overall list does not exceed 255 fields.

So you better put this data together in an excel sheet or similar, that's not limiting your column count, practically.

You may also use MSSQL backend. That still limits single cursors and SET FIELD lists to 255 columns, but you can surely process and report more fields (with SET RELATION in VFP you can bind even three or more tables), but use third party controls for display. You could show a HTML table as grid. If you really have that much you absolutely wan to look at in parallel, I can't see the benefit of such overviews, even if it's just boolean flags.

Bye, Olaf.