comparing two integers and returning a third

I am having difficulty trying to figure out how to compare two integers stored in a table to return a third. I have two integer fields in one table and two in another like this:

Table1.SomeNumber1 = 1

Table1.SomeNumber2 = 2

Table2.SomeNumber1 = 2

Table2.SomeNumber2 = 1

I need to be able to compare the first number from the first table to the first number in the second table. If the values are different I need to set a variable or field to 0. If the numbers are the same I need to set my variable or field to 1.

I need to follow the same procedure comparing the second number in the first table to the second number in the second table. In addition, I need to be able to do it in a single select statement.

Does anyone have any ideas on how this could be done? Thank you for any help you may be able to provide.

Gmz

CREATE TABLE [dbo].[num1](
[id] [int] NULL,
[number1] [int] NULL,
[number2] [int] NULL,
[flag1] [int] NULL,
[flag2] [int] NULL
)
CREATE TABLE [dbo].[num2](
[id] [int] NULL,
[number1] [int] NULL,
[number2] [int] NULL
)
--After the comparison, two flag fields in table num1 are updated
UPDATE num1
SET flag1=(SELECT (CASE WHEN a.number1 = b.number1 THEN 1 ELSE 0 END) FROM num1 a INNER JOIN
num2 b ON a.id = b.id and a.id=c.id), flag2=(SELECT (CASE WHEN a.number2 = b.number2 THEN 1 ELSE 0 END) FROM num1 a INNER JOIN
num2 b ON a.id = b.id and a.id=c.id)
FROM num1 c|||

I took what you posted and applied it to just return the values of the comparisons as results of my query which saved me from having to store the extra data in the table. It worked great.

Thank you!!!!

GMZ

sqlsql

Comparing queries for flagging conflicts

Hello all... I'm stuck, I cannot figure out how I should go about flagging
conflicts on a sheduling app. I currently have 8 columns (school grades)
that have class over the course of 9 periods. I am populating the asp page
fine, and making changes to the database with forms lists. I need to compare
all the results of one period (thats 8 results) so that i may find a
classroom conflict. Is there any solution in SQL?

This is my query:
sql = "SELECT * FROM schedule WHERE period ='"&num&"'"
I step through this 9 times in a for/next loop

Thanks in advance!
Alpay EnoAlpay Eno (eno@.spamsux.com) writes:
> Hello all... I'm stuck, I cannot figure out how I should go about
> flagging conflicts on a sheduling app. I currently have 8 columns
> (school grades) that have class over the course of 9 periods. I am
> populating the asp page fine, and making changes to the database with
> forms lists. I need to compare all the results of one period (thats 8
> results) so that i may find a classroom conflict. Is there any solution
> in SQL?

Dunno. If you post:
o CREATE TABLE statement(s) for the involved table(s)
o INSERT statements with sample data
o The desired output from that sample data

there are odds that you will get a more precise answer.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Comparing columns in two tables

Hi DBA's,

I need to figure out how to compare the column data in two distinct tables. I have two files that populate these two tables. Basically I am doing a file comparison here. Let me explain the process:

Table 1

Col 1 Col 2

ID Name

1 A
2 C,D
3 F

Table 2

Col

Name

E
F
D

Now if there is any data that is present in Table 2 that matches with the data in table 1 then I need to write the entire record of table 2 into a separate table OR file.

Here is what I think I need to do.

1. Take first record from Table 1 and scan Table 2 to see if the Name 'A' exists. If yes put/insert the record from Table 2 in a seprate table say table 3 and then go to the second record. If no match then go directly to the second record in table 1. Repeat the process till every record in table 1 is compared to the records in table 2.

2. Now the trick here is some Names have only last name. Others have last name and first name. So for Table 1, Name C,D should be a match to D in Table 2. I have to send this record to Table 3. How do I accomplish that? Should I spilt the Col2 into columns. How do I do that?

Please note that table 2 would have close to 5000 records.

Please advise.

Thanks in anticipation.Think you'll be having a lot of false matches on Smtih and Jones...

Maybe not Kaiser though...

Can you post the DDL of the Tables, and sample data..like

CREATE TABLE myTable99 (Col1 int, Col2, varchar(50), ect

For Sample Data, something like..

INSERT INTO myTable99 (Col1, Col2, ect)
SELECT 1, 'Brett Kaiser', ect UNION ALL
SELECT 2, 'Indiana Jones', ect UNION ALL
SELECT 3, 'Jones', ect UNION ALL
SELECT 4, 'Jeff Smith', ect UNION ALL

get the picture?

It's easier to help when we have the actual stuff...

Still think the matching will be a fudge though..

maybe you can match on exact, remove that population, then do the fudge on a smaller subset...sqlsql