Unique matches in two tables

[liark]

Here is my problem:

I have two tables, each table contains ten unique letters.

How can I compare the two tables to give me the number of matches?

eg table 1 has:

q, w, e, r, t, y, u, i, o, p

and table 2 has:

z, t, q, g, j, a, u, s, r, m

The answer I need is: 4

I don't need to know which ones matched.

In my real example the letters are words (from a predefined list) but the principle is the same.

Many thanks for your help.

Liark

 

[TruthInSatire]

something like

select table1.field1
from table1
where table1.field1 = table2.field1

<cfoutput>The number of matching fields is #queryName.recordCount#</cfoutput><br>
the matching letters are...<br>
<cfoutput query = "queryName">
#queryName.field1#<br>
</cfoutput>

Beware of programmers who carry screwdrivers.

 

[r937]

okay, i suppose that if there are only 10 rows in each table, it shouldn't matter that you actually return all the rows and let CF count them

however, it is more efficient to let the database count them, and return only one row with the answer to CF

plus, you'll need a join to do the matching

<cfquery name="howmany" datasource="foo">
  select count(*) as matches
    from table1
  inner
    join table2
      on table1.field1 = table2.field1
</cfquery>      

<p>The number of matching fields is 
<cfoutput query="howmany">#matches#</cfoutput></p>

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[liark]

Thanks for your help, guys. I'll elaborate a bit - both tables have many records.

There is one record in table 1 (containing ten fields) which corresponds to many records in table 2 (containing ten fields). The records are linked by a key.

So table 1 will have a key value (e.g. 1234) and in table 2 there will be many records (each having 10 fields) with the key 1234 (and the record owners name).

I need to read each record in table 2 that has the 1234 key and compare it to the master record in table a, giving me the owner and how many fields match the master record in table 1.

Remember both the master record and the user's record can have any of the 26 values in any order.

Thanks again for any help.

Liark

 

[r937]

ah, i see

your table design is not in first normal form

any time you have multiple values inside a single column, you are asking for a world of hurt

in this particular instance, you could do a join based on the key, return all rows that match, then compare the ten-value columns using coldfusion arrays... somehow...

inefficent and slow, yes?

could you redesign the table? that would make the query a lot more efficient

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[liark]

OK, thanks. I have it working - I wrote the code a couple of years ago, but you're right - it is a world of hurt. I keep coming back to it trying to think of ways to normalise the tables.

On day I will do a complete rewrite - as with a lot of things it grew organically, so I didn't really know where I would end up when I started.

In fact, I'm quite glad you have advised me that the world of hurt is where I'm at - it just means that when I redo it I will HAVE to redesign the databases.

Cheers.


Liark