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
 No match rows from 2 tables w different id sets
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Neftoma
Starting Member

12 Posts

Posted - 09/30/2012 :  14:15:55  Show Profile  Reply with Quote
Hi Everyone!
Please, help needed with the following: I have two tables T1 and T2. I need to get a resulting table where for each fund from T2 with a specific date, client and manager I would have firms which are in T1 but not in T2 for the corresponding date, client and manager . The identifier “fund” unfortunately doesn’t exist in T1.
With my query I get all firms which do not exist in T2 at all, and this is not what I need.
select clientcode, manager, dates, firm, fund
from T1
left outer join T2
on T1.client=T2.client and T1.manager=T2.manager and T1.date=T2.date
and T1.firm=T1.firm
where T2.firm is NULL

T1
client manager date firm
1 123 31.03.1999 a
1 123 31.03.1999 b
1 123 31.03.1999 c
1 123 31.03.1999 d
1 123 31.03.1999 e
1 123 31.03.1999 f
1 123 31.03.1999 g

T2
client manager date firm fund
1 123 31.03.1999 a F
1 123 31.03.1999 b F
1 123 31.03.1999 c F
1 123 31.03.1999 d F
1 123 31.03.1999 a M
1 123 31.03.1999 c M
1 123 31.03.1999 f M



Desired resulting table
client manager date firm fund
1 123 31.03.1999 e F
1 123 31.03.1999 f F
1 123 31.03.1999 g F
1 123 31.03.1999 b M
1 123 31.03.1999 d M
1 123 31.03.1999 e M
1 123 31.03.1999 g M
I get instead
1 123 31.03.1999 g

Edited by - Neftoma on 09/30/2012 14:31:17

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/30/2012 :  18:44:39  Show Profile  Reply with Quote
Do you have a list of valid funds in another table? If you don't have that, and if your table had no data at all for a given fund (fund F, for example), how would you even know that you need to list the seven rows for that fund in the output?
Go to Top of Page

Neftoma
Starting Member

12 Posts

Posted - 10/01/2012 :  08:38:47  Show Profile  Reply with Quote

What I am actually doing is I am matching 2 databases on "firms". One database has fund identifier “fund”, another database has 2 variables identifying a fund: "client"+"manager" (I don’t have the names of the funds, so I match on firms they hold). T1 is database A, T2 is database A+B containing all entries where firms from A and B matched. What I need at this step is to create a table which would contain identifiers from both funds with firms which DID NOT match between the 2 databases A and B.

quote:
Originally posted by sunitabeck

Do you have a list of valid funds in another table? If you don't have that, and if your table had no data at all for a given fund (fund F, for example), how would you even know that you need to list the seven rows for that fund in the output?

Go to Top of Page

Neftoma
Starting Member

12 Posts

Posted - 10/01/2012 :  10:04:03  Show Profile  Reply with Quote
Thanks to all! The problem is solved: i mande another couple of inner joins.
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.09 seconds. Powered By: Snitz Forums 2000