| Author |
 Topic  |
|
|
Neftoma
Starting Member
12 Posts |
 Posted - 09/30/2012 : 14:15:55
|
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
5152 Posts |
Posted - 09/30/2012 : 18:44:39
|
| 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? |
 |
|
|
Neftoma
Starting Member
12 Posts |
Posted - 10/01/2012 : 08:38:47
|
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?
|
|
|
|
Neftoma
Starting Member
12 Posts |
Posted - 10/01/2012 : 10:04:03
|
| Thanks to all! The problem is solved: i mande another couple of inner joins. |
|
|
| |
Topic |
|
No match rows from 2 tables w different id sets
