# Data Comparison

**URL:** <https://www.ruby-forum.com/t/data-comparison/128478>\
**Category:** Rails\
**Created:** [February 8, 2008, 12:19am UTC](https://www.ruby-forum.com/t/data-comparison/128478 "2008-02-08T00:19:08Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![sithadmin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/858c86/32.png) [@sithadmin](https://www.ruby-forum.com/u/sithadmin)\
**Post date:** [February 8, 2008, 12:19am UTC](https://www.ruby-forum.com/t/data-comparison/128478/1 "2008-02-08T00:19:08Z")

</div>

Hello,  
I am having trouble trying to get a data table comparison working… I  
have two database tables with their own models. Both contain phone  
number information. What I need the program to do is to read every phone  
number from every row from Table1 and compare that number to every  
number in every row in Table2. It then needs to put that information in  
two variables. One containing all the entries that match in both tables  
and one containing all the entries that do not match. I am very new to  
RoR and I can do this no problem in PHP, but my boss wants it in Ruby…  
I have a “compare” controller in which all this should take place. I  
have tried using find\_by\_sql with no luck… I think my big problem is  
simply not defining variables properly or something. My controller looks  
like this: Btw, “bes” is one table (holding Blackberry phone info) and  
“prov” is another table (holding info from our wireless provider).

def index  
@bes = Bes.find(:all)  
@prov = Import.find(:all)

@provnumber = Import.prov\_service\_number  
@matches = Bes.find\_by\_sql(“SELECT bes\_phonenumber number,  
bes\_displayname name FROM bes WHERE bes\_phonenumber = @provnumber”)  
end

My view has something like this:

\<% for match in @matches -%\>  
\<% @matches %\>

 \<%= match.name %\> \<%= match.number %\> \<%= match.prov\_number %\> \<% end %\>

Thanks for all your help! I appreciate it!

- Jeff

---

<div class="post-metadata">

**Author:** ![sithadmin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/858c86/32.png) [@sithadmin](https://www.ruby-forum.com/u/sithadmin)\
**Post date:** [February 8, 2008, 1:26am UTC](https://www.ruby-forum.com/t/data-comparison/128478/2 "2008-02-08T01:26:27Z")

</div>

On Feb 7, 2008, at 6:19 PM, Jeff M. wrote:

> and one containing all the entries that do not match. I am very new to  
> @bes = Bes.find(:all)  
> \<% for match in @matches -%\>  
> \<% @matches %\>
> 
> \<%= match.name %\> \<%= match.number %\> \<%= match.prov\_number %\> \<% end %\>
> 
> Thanks for all your help! I appreciate it!
> 
> - Jeff

Well, you can’t get much help if you don’t provide enough  
information. Perhaps if you showed what the models look like or the  
columns from your tables. As a start, an ActiveRecord model called  
“Be” would keep its records in a table named ‘bes’.

class Be \< ActiveRecord::Base  
end

If you had SQL that gave you the results that you wanted, you could  
either get a list of bes.id or skip the whole model thing and jump  
straight to the adapter:

@matches = Be.connection.select\_all(“SELECT bes.bes\_phonenumber AS  
number, bes.bes\_displayname AS name, prov.number AS prov\_number FROM  
bes JOIN prov ON bes.bes\_phonenumber = prov.number”)

which would give you an array of hashes that are keyed by ‘number’,  
‘name’, and ‘prov\_number’

 \<% for match in @matches -%\> \<% end -%\> 

| Name | Number | ProvNumber |
| --- | --- | --- |
| \<%= match['name'] %\> | \<%= match['number'] %\> | \<%= match['prov\_number'] %\> |

But if this doesn’t get you closer to a solution, ask again WITH MORE  
DETAIL. In particular, you probably need to do an OUTER JOIN to find  
the records that lack a match in the other table. If you need to get  
the unmatched records in both directions, you likely need two separate  
OUTER JOIN queries.

-Rob

Rob B. [http://agileconsultingllc.com](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![sithadmin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/858c86/32.png) [@sithadmin](https://www.ruby-forum.com/u/sithadmin)\
**Post date:** [February 8, 2008, 3:04am UTC](https://www.ruby-forum.com/t/data-comparison/128478/3 "2008-02-08T03:04:53Z")

</div>

Thank you very much for that detailed response! It clarified a lot for  
me. Sorry I didn’t include enough information… I am very new to RoR  
and I haven’t used any kind of MVC type framework before. Both my models  
are empty (I don’t really know what I would put in them…) and table1  
is called ‘bes’ and table2 is called ‘imports’. Everything seems to be  
working great! I tried to get the OUTER JOIN sql statement to work, but  
it actually gives me both matches and non-matches, instead of just  
non-matches. I know its not really a Ruby deal, but do you know the  
correct statement? I’ve been trying to look it up, but no luck so far.

@nonmatches = Bes.connection.select\_all(“SELECT bes.bes\_phonenumber AS  
number, bes.bes\_displayname AS name, imports.prov\_service\_number AS  
prov\_number FROM bes LEFT OUTER JOIN imports ON bes.bes\_phonenumber =  
imports.prov\_service\_number”)

Thanks dude! I really appreciate your help!

---

<div class="post-metadata">

**Author:** ![sithadmin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/858c86/32.png) [@sithadmin](https://www.ruby-forum.com/u/sithadmin)\
**Post date:** [February 8, 2008, 2:07pm UTC](https://www.ruby-forum.com/t/data-comparison/128478/4 "2008-02-08T14:07:24Z")

</div>

On Feb 7, 2008, at 9:04 PM, Jeff M. wrote:

> non-matches. I know its not really a Ruby deal, but do you know the  
> correct statement? I’ve been trying to look it up, but no luck so far.
> 
> @nonmatches = Bes.connection.select\_all("SELECT bes.bes\_phonenumber AS  
> number, bes.bes\_displayname AS name, imports.prov\_service\_number AS  
> prov\_number FROM bes LEFT OUTER JOIN imports ON bes.bes\_phonenumber =  
> imports.prov\_service\_number

WHERE imports.prov\_service\_number IS NULL

> ")
> 
> ## Thanks dude! I really appreciate your help!

You need to select only those rows that exist in the result due to the  
addition of the null record from the other table (because of the  
OUTER). It may not be something easily looked up, but it should be  
easy enough to understand and apply later.

-Rob

Rob B. [http://agileconsultingllc.com](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid