Need help with a join query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help with a join query

Author

Topic

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-05-27 : 10:24:03

Just when I thought I was beginning to live, think, and breath SQL, I'm having my doubts once again.

I have two tables (bigtable, smalltable) of identical structure:

Date ID Value

with different data. I have successfully inner-joined them as so:

select a.*
from smalltable a,
bigtable b
where a.ID = b.ID
and a.Date = b.Date

thus giving me the results of the smaller subset (smalltable, as bigtable is a superset of smalltable).

However, I'd like to generate an exception report which shows which records from bigtable got excluded from my inner join to the smalltable. Could anyone show me how to structure such a query with given the two columns Date & ID, please?

Thank you in advance.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-27 : 10:49:58

seems to you you haven't even begun to breathe sql

1. Use join syntax
2. Use LEFT JOIN for what you need.
3. try it and if you have any problems post it here.

Go with the flow & have fun! Else fight the flow

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-05-27 : 11:17:34

Hi,

Thanks for your suggestion.

I actually thought of using this, but isn't there another way to do it? Specifically, there must be a way to join on both Date & ID fields and select where not in the other?

I need to know this because it will help me deal with other situations, otherwise I could use '*=' and select where null that isn't part of the subset.

Any ideas or is this just not done at all? (By the way, you're right, I'm not even breathing it yet).

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-27 : 11:21:37

well you can use not exists
select ...
from table1 t1
where not exists (select * from table2 where t1.id = id)

Go with the flow & have fun! Else fight the flow

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-05-27 : 11:36:25

Hey, thanks for that suggestion! I'll give it a try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources