Need help with Not-in queries.

[hneal98]

Hi,

I know how to do a not in if I am just dealing with one field, but what if I have the following situation; how should I handle that?

I have two tables that would link on id, transaction_code and Transaction_date. For any record, any of these fields could be different. for example:

Table1 Table2
1 1/1/08 001 1 1/5/08 001
2 1/5/08 005 2 1/5/08 007
3 1/7/08 003 3 1/7/08 003
4 2/12/08 006 4 2/15/08 006

I want to create a query that gives me all records in Table1 where these fields are exactly the same also in Table2.

The result from the above example would be:

3 1/7/08 003

I can't seem to wrap my head around this particular issue.

Any help is greatly appreciated.

Thanks,

Neal

 

[SQLDenis]

look up INNER JOIN in BOL

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[hneal98]

I am sorry, what is BOL?

 

[SQLDenis]

books on line

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

faq183-689
faq183-4785

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[hneal98]

I know what Books on Line is. However, I don't know how searching for Inner Join would help.

Perhaps someone else knows how to do this.

Thanks.

 

[hneal98]

Actually, I miss-stated what I needed. What I need is to find what is in one table that is not in the other. I wrote this up too fast without thinking about it.

A normal not-in query would be something like this:

Select id, trc, tran_date
from trans_table tt
left join trans_stage ts on tt.id = ts.id
where
ts.id is null

However; the issue I am having is that there are three fields that need to be checked to make sure they are all null. That is what I need to know.

Sorry for the confusion.

Thanks,

Neal

 

[SQLDenis]

put all the 3 fields columns in the JOIN condition

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions