MapInfo Pro

I have two tables that have a userId as a common join. I want to bring the data from Column A to New Column Table 2 it only brings in the first instance and repeats it (see below)

Table1................................Table2

Column A.... UserID...........UserId.......New Column

Terrtory1.........AA................ AA...........Territory1

Territory2.........AA.................AA...........Territory1

Territory3.........AA.................AA...........Territory1

Territory4........ AA.................AA...........Territory1

Territory5.........BB..................BB..........Territory5

Territory6.........BB..................BB...........Territory5

Territory7.........BB..................BB...........Territory5

Territory8.........BB..................BB...........Territory5

Can someone help me with this or am i even trying to do this the right way

Thank You

Hi Raymond,

Can you remove duplicates from Table 2 without loss of data? That is, if you have 4 records with UserID of "AA", remove three of those records via a Group By query. Then perform a join to Table 1 and save the results.

If that is not possible, you could try something like:

Select * from Table1, Table2 where Table1.UserID=Table2.UserID into JoinedTable

Update JoinedTable Set NewColumn = NewColumn + "," + ColumnA

This will update NewColumn with a comma-separated list, e.g:

",Territory1,Territory2,Territory3,Territory4"

after which you will need to parse this via a number of iterations.

@Raymond Cordova? You need to have a Unique records column if you are trying to do the this. SQL will search through index and it will take the first matched record and populate the results. If you have same number of records and objects are in same order as the other table you will be able to achieve this. I would suggest you to create a Unique Id column for both tables and do the Join operation. Good luck!

Reniel, Thank you for the reply, I did use the update column tool and the result was taking the first record and repeating it as in my example.

James, Thank you also for the reply, unfortunately, I can't remove the duplicates. But your response will may help me to further manipulate the files I am working on!

Krishna, Thank you for the reply, I contemplated your solution, however there are UserId's not in both tables, so I'm not sure this will work, but I can play with it.

Prior to and after posting this question I was reading everything on this site, all the Ninja Query articles and just a bunch of questions already on here. I have used Mapinfo for almost a year, but I'm in a new position and trying to help my new workgroup find some data solutions to projects they are working on using Mapinfo as tool. Up until now I have never had to go so deep into Mapinfo, SQL, & Mapbasic.

I may have found a solution using a double join of USERID and Territory and taking that query and updating my second table using the territory as the join back into my second table. I am auditing the results now to see if this gives me the results I am looking for.