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.
| Author |
Topic |
|
Neftoma
Starting Member
12 Posts |
 Posted - 2012-09-30 : 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, fundfrom 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 NULLT1 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 fund1 123 31.03.1999 a F1 123 31.03.1999 b F1 123 31.03.1999 c F1 123 31.03.1999 d F1 123 31.03.1999 a M1 123 31.03.1999 c M1 123 31.03.1999 f M Desired resulting table client manager date firm fund1 123 31.03.1999 e F1 123 31.03.1999 f F1 123 31.03.1999 g F1 123 31.03.1999 b M1 123 31.03.1999 d M1 123 31.03.1999 e M1 123 31.03.1999 g M I get instead 1 123 31.03.1999 g |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-30 : 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 - 2012-10-01 : 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 - 2012-10-01 : 10:04:03
|
| Thanks to all! The problem is solved: i mande another couple of inner joins. |
|
|
|
|
|
|
|
|
No match rows from 2 tables w different id sets