7 Replies - 2357 Views - Last Post: 30 September 2008 - 03:58 PM Rate Topic: -----

#1 jeffmc21   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 64
  • Joined: 27-November 07

Comparing fields from two DB tables with CF

Posted 24 September 2008 - 09:19 PM

Hey guys..

I have two database tables. I want to compare fields from tbl_one with fields from tbl_two and the output the resulting similarities.

So, I want to take table_one.field_one and compare it to table_two.field_one. If they match, I'd want to output something like "CORRECT", and if not "INCORRECT". Or even better might be to simply compare the 10 fields from table one to the corresponding ten fields in table two and then output the number of matching fields.

Any help?

This post has been edited by jeffmc21: 24 September 2008 - 09:20 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Comparing fields from two DB tables with CF

#2 sansclue   User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: Comparing fields from two DB tables with CF

Posted 25 September 2008 - 08:24 AM

quote name='jeffmc21' date='24 Sep, 2008 - 09:19 PM' post='423580']
Hey guys..

I have two database tables. I want to compare fields from tbl_one with fields from tbl_two and the output the resulting similarities.

So, I want to take table_one.field_one and compare it to table_two.field_one. If they match, I'd want to output something like "CORRECT", and if not "INCORRECT". Or even better might be to simply compare the 10 fields from table one to the corresponding ten fields in table two and then output the number of matching fields.

Any help?
[/quote]

How are you identifying matching records in the two tables? Do they have a shared UK (unique key)?

Also, how do you wish to handle cases where a record exists in table_one, but not in table_two? Or vice versa.
Was This Post Helpful? 0
  • +
  • -

#3 jeffmc21   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 64
  • Joined: 27-November 07

Re: Comparing fields from two DB tables with CF

Posted 28 September 2008 - 09:52 PM

View Postsansclue, on 25 Sep, 2008 - 08:24 AM, said:

How are you identifying matching records in the two tables? Do they have a shared UK (unique key)?

Also, how do you wish to handle cases where a record exists in table_one, but not in table_two? Or vice versa.


The beginnings of my problem comes in the fact that I'm not sure what a UK does, is, or should be. However, I probably need to figure that out, as I'm sure it is something that will not only help here, but possibly other db issues in the future.

As for the cases where records exist in one table but not the other, that case should never exist, however, if that case did, I'd want to disregard that "set of results".

To be more specific about what I'm trying to do:
I want to match the results from column_4 of table_one against data from column_3 thru column_8 of each row in table_two.

See the attached diagram that makes more sense. (not to exact scale of db tables)

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#4 sansclue   User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: Comparing fields from two DB tables with CF

Posted 29 September 2008 - 09:31 AM

View Postjeffmc21, on 28 Sep, 2008 - 09:52 PM, said:

The beginnings of my problem comes in the fact that I'm not sure what a UK does, is, or should be. However, I probably need to figure that out, as I'm sure it is something that will not only help here, but possibly other db issues in the future.


A UK (unique key) is a value(s) that can be used to uniquely identify a record. For example, if you have a table of users and it contains ten different users, all named "John Smith". A unique key or ID would tell you which one was which.

In order to compare records from the two tables they need to have a key or something in common. Otherwise you would not know which two records to compare. So what key or values do these two tables have in common?

This post has been edited by sansclue: 29 September 2008 - 09:32 AM

Was This Post Helpful? 0
  • +
  • -

#5 jeffmc21   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 64
  • Joined: 27-November 07

Re: Comparing fields from two DB tables with CF

Posted 30 September 2008 - 09:28 AM

View Postsansclue, on 29 Sep, 2008 - 09:31 AM, said:

A UK (unique key) is a value(s) that can be used to uniquely identify a record. For example, if you have a table of users and it contains ten different users, all named "John Smith". A unique key or ID would tell you which one was which.

In order to compare records from the two tables they need to have a key or something in common. Otherwise you would not know which two records to compare. So what key or values do these two tables have in common?


I haven't "set" a unique key yet, but can. Each of the rows in table one has a column named 'game_id', and the fields in this column are populated with '1', '2', '3', etc..

Is that an identifier? Then the columns, for comparison, in table two are called 'game_1', 'game_2', etc..

Would it be easier if I were comparing rows in the same table, with the same columns? Because I can do that instead.
Was This Post Helpful? 0
  • +
  • -

#6 sansclue   User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: Comparing fields from two DB tables with CF

Posted 30 September 2008 - 11:17 AM

View Postjeffmc21, on 30 Sep, 2008 - 09:28 AM, said:

I haven't "set" a unique key yet, but can. Each of the rows in table one has a column named 'game_id', and the fields in this column are populated with '1', '2', '3', etc..

Is that an identifier?


It could be if each game_id is only used once. By definition a UK or PK (Primary key) must be unique. So only one record can have game_id = 1 or game_id = 2, etc.

But usually the easiest option is to just use an identity column for a UK. Add one to your table and make it the PK (primary key). Whenever you insert a new record, MS SQL will increment the value automatically. You do not have to worry about uniqueness or incrementing. There are some exceptions, but it is usually the right choice.

CREATE TABLE YourTable
(
game_id int identity(1,1) PRIMARY KEY,
otherColumn varchar(100),
.....
)

View Postjeffmc21, on 30 Sep, 2008 - 09:28 AM, said:

Then the columns, for comparison, in table two are called 'game_1', 'game_2', etc..

Would it be easier if I were comparing rows in the same table, with the same columns? Because I can do that instead.


Most likely, yes. Usually when you have a table with the same column repeated multiple times (something1, something2, something3, etc), it is a sign you need to normalize and restructure the data into row format.

What are the relationships of the game_id records and what type of comparisons do you need to perform?
Was This Post Helpful? 0
  • +
  • -

#7 jeffmc21   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 64
  • Joined: 27-November 07

Re: Comparing fields from two DB tables with CF

Posted 30 September 2008 - 01:26 PM

View Postsansclue, on 30 Sep, 2008 - 11:17 AM, said:

It could be if each game_id is only used once. By definition a UK or PK (Primary key) must be unique. So only one record can have game_id = 1 or game_id = 2, etc.

But usually the easiest option is to just use an identity column for a UK. Add one to your table and make it the PK (primary key). Whenever you insert a new record, MS SQL will increment the value automatically. You do not have to worry about uniqueness or incrementing. There are some exceptions, but it is usually the right choice.

CREATE TABLE YourTable
(
game_id int identity(1,1) PRIMARY KEY,
otherColumn varchar(100),
.....
)

Most likely, yes. Usually when you have a table with the same column repeated multiple times (something1, something2, something3, etc), it is a sign you need to normalize and restructure the data into row format.

What are the relationships of the game_id records and what type of comparisons do you need to perform?


I would like to compare winners of games, with a user(s) pick for the winners. So table one would actually be the table that held the fields that populate the form for users entries (picks) to be made. The entries would then be inserted into the second table. So, at the end of the week, I'd want to check a column from table_one, the winners we input for each game (row), against the users picks for each game (col1, col2, col3, etc) from table_two.

However, if it'd be easier to do the comparisons by making the winners "column" from table one into a row, just like the user picks would be, in table two, I can do that.
Was This Post Helpful? 0
  • +
  • -

#8 sansclue   User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: Comparing fields from two DB tables with CF

Posted 30 September 2008 - 03:58 PM

View Postjeffmc21, on 30 Sep, 2008 - 01:26 PM, said:

I would like to compare winners of games, with a user(s) pick for the winners. So table one would actually be the table that held the fields that populate the form for users entries (picks) to be made. The entries would then be inserted into the second table. So, at the end of the week, I'd want to check a column from table_one, the winners we input for each game (row), against the users picks for each game (col1, col2, col3, etc) from table_two.

However, if it'd be easier to do the comparisons by making the winners "column" from table one into a row, just like the user picks would be, in table two, I can do that.


Then I would definitely go with a row format instead of columns. In table_two I would store each of the user's entries in a separate row. Something like this where each row contains the user_id, game_id and the winning team id. (I am assuming here the team information comes from a separate table with its own ID)

UserID, GameID, WinningTeamID
22 (Bob), 1 (Game One), 2 (Team Blue)
22 (Bob), 2 (Game Two), 3 (Team Alpha)
22 (Bob), 3 (Game Three), 1 (Team Green)
22 (Bob), 4 (Game Four), 9 (Team Omega)
22 (Bob), 5 (Game Five), 4 (Team Gold)
22 (Bob), 6 (Game Six), 3 (Team Alpha)

Then you could easily compare the results by doing a JOIN with table_one on the UK (ie game_id) and using a CASE to see if there is a match on the winner id column.

GameID, WinningTeamID
1 (Game One), 2 (Team Blue)
2 (Game Two), 4 (Team Gold)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1