×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Merging two table's fields

Merging two table's fields

Merging two table's fields

(OP)
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!

RE: Merging two table's fields

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.

RE: Merging two table's fields

(OP)
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?

RE: Merging two table's fields

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



CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close