Selecting not matched rows from 2 tables

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

General SQL Server Forums

New to SQL Server Programming

Selecting not matched rows from 2 tables

Author

Topic

Neftoma
Starting Member

12 Posts

Posted - 2012-09-23 : 15:52:24

Hi Everyone,
Please help!
I have 2 tables “big” and “small”. I would like to get an output table which would contain client, manager and date contained in both small and big, but stock number being existing only in Big. I’m getting wrong results instead.
Here’s my code:
select b.client,b.manager,b.year, b.quarter,s.stock,s.date
from big b
left outer join small s
on b.client=s.client and b.manager=s.manager and b.year=DATEPART(year,s.date) and b.quarter=DATEPART(quarter,s.date)
where s.stock is NULL;

Small
client manager date stock
485 13630 1999-03-31 10606
485 13630 1999-03-31 10843
485 13630 1999-03-31 20765

Big
client manager year quarter stock
485 13630 1999 1 10606
485 13630 1999 1 10843
485 13630 1999 1 18091
485 13630 1999 1 20765
485 13630 2003 2 12345

Expected result:
485 13630 1999 1 18091

I'm getting instead:
485 13630 2003 2 12345

Thank you in advance!

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-23 : 16:20:43

you need to join the stock field as well:


select b.client
      ,b.manager
      ,b.year
      ,b.quarter
      ,s.stock
      ,s.date
  from big as b
       left outer join small as s 
                    on s.client=b.client
                   and s.manager=b.manager
                   and DATEPART(year,s.date)=b.year
                   and DATEPART(quarter,s.date)=b.quarter
                   and s.stock=b.stock
 where s.stock is NULL

Edit: ohh I forgot to mention, that you should expect:
485 13630 1999 1 18091
485 13630 2003 2 12345

Neftoma
Starting Member

12 Posts

Posted - 2012-09-23 : 16:27:32

Thank you!!! Great!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources