How to Compare two tables in perl script

[anjalive]

Hello,

Can we compare two tables in perl? I mean How can we do it for database tables.

I would like to know the solution for below scenario in perl script.

Here are 2 tables:
Table 1
Names City
aaaaa hyd
bbbbb bang
ccccc chen
dddddd noida
eeeeeee hyd
ffffffffffff chen

Table: 2
Names Organization
aaaaa TCS
bbbbb Infosis
sssss TCS
dddddd Infosis
eeeeeee NTT
ccccc NTT


When i compare this tables: When i compare Names of two columns, the result should be like this:
Name City Organization
aaaaa hyd TCS
bbbbb bang Infosis
ccccc chen NTT
dddddd noida Infosis

I need organization details from table 2 which has same name in both table 1 and 2.

Thank you for help !

[RonB]

What have you tried?

What part of the task is giving you trouble?

What errors/warnings are you receiving?

I have a feeling that this is some sort of homework assignment, so I'll hold off providing a full solution. However, I will give you a hint.

Load one of the files into a hash to be used as a lookup table.

[anjalive]

Hello Ron,

I am planning to do automation for macros which is in MS Access DB into Perl Scriot. There in Access DB, we used to compare IP column which is same in both tables and will retrive details whatever required. I got struck at comparing tables. I am not getting idea how to compare. Asking for help this.

Thank you

[RonB]

The sql statement you need would be:

SELECT t1.Names, t1.City, t2.Organization FROM table1 as t1 INNER JOIN table2 as t2 ON t1.Names = t2.Names;

As a personal preference in coding style, I prefer to break long statements (greater that 80 characters) like that into multiple lines to make it more readable and maintainable.

SELECT t1.Names, t1.City, t2.Organization
FROM table1 as t1
INNER JOIN table2 as t2 ON t1.Names = t2.Names;
 

Here's the actual query and it's results.

mysql> SELECT t1.Names, t1.City, t2.Organization
    -> FROM table1 as t1
    -> INNER JOIN table2 as t2 ON t1.Names = t2.Names;
+---------+-------+--------------+
| Names   | City  | Organization |
+---------+-------+--------------+
| aaaaa   | hyd   | TCS          |
| bbbbb   | bang  | Infosys      |
| ccccc   | chen  | NTT          |
| dddddd  | noida | Infosys      |
| eeeeeee | hyd   | NTT          |
+---------+-------+--------------+
5 rows in set (0.04 sec)
 

If you need help in using that statement in your script, then you will need to post your script.

[anjalive]

Thank you for the reply RonB. Appreciated.

I have one more question.

I just created a script with db tables and output was moved to excel sheets.
Below are two sheets:

Sheet1:

IP address Status
x.x.x.x success
y.y.y.y No Access
z.z.z.z Retired
a.a.a.a Success

Sheet2:
IP address Status
x.x.x.x success
y.y.y.y Success
z.z.z.z NoAccess
a.a.a.a NoAccess

Now, I have to compare and update sheet1 status with sheet2. Sheet1 has oen IP with retired status and for same IP sheet2 has NoAccess. In result the retired status should not change:

The output seems to be like this:

IP address Status
x.x.x.x success
y.y.y.y Success
z.z.z.z Retired
a.a.a.a NoAccess.

If its in database, we can write update and where clause as not like.

But how to compare and update in Perl script ?

Thank you for the help !

[RonB]

What have you tried?

Your question is not very clear. Please post your script and explain how it fails to do what you need. Include any errors/warnings it produces.