SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Selecting not matched rows from 2 tables		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Neftoma
Starting Member

12 Posts
Posted - 09/23/2012 :  15:52:24  Show Profile  Reply with Quote
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!
Edited by - Neftoma on 09/23/2012 15:58:54

bitsmed
Yak Posting Veteran

Denmark
98 Posts
Posted - 09/23/2012 :  16:20:43  Show Profile  Reply with Quote
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
Edited by - bitsmed on 09/23/2012 16:24:48
Go to Top of Page

Neftoma
Starting Member

12 Posts
Posted - 09/23/2012 :  16:27:32  Show Profile  Reply with Quote
Thank you!!! Great!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000