Join 136,835 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,748 people online right now. Registration is fast and FREE... Join Now!
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 Sep, 2008 - 08:20 PM
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.
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)
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 Sep, 2008 - 08:32 AM
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.
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.
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?
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.
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.
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)
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.
| 