SQL Join Ignore multiple matches (fuzzy results are OK)

Question

I don’t even know what the name of my problem is called, so I just collect some sample data. I am not opposed to fuzzy results on this (this is the best way I can think to express this. I do not mind if I miss some data, this is for a rough estimate, and not for detailed accounting, if that makes sense). But I need every entry in TABLE 1, and I would like to avoid the nulls case below.

IS IT POSSIBLE?

TABLE 1 acctnum sub fname lname phone 12345 1 john doe xxx-xxx-xxxx 12346 0 jane doe xxx-xxx-xxxx 12347 0 rob roy xxx-xxx-xxxx 12348 0 paul smith xxx-xxx-xxxx TABLE 2 acctnum sub division 12345 1 EAST 12345 2 WEST 12345 3 NORTH 12346 1 TOP 12346 2 BOTTOM 12347 2 BALLOON 12348 1 NORTH 

So, if we make a “regular external” join, we get some results like this, since sub 0 does not match the second table:

 TABLE AFTER JOIN acctnum sub fname lname phone division 12345 1 john doe xxx-xxx-xxxx EAST 12346 0 jane doe xxx-xxx-xxxx null 12347 0 rob roy xxx-xxx-xxxx null 12348 0 paul smith xxx-xxx-xxxx null 

But I would rather get

 TABLE AFTER JOIN acctnum sub fname lname phone division 12345 1 john doe xxx-xxx-xxxx EAST 12346 0 jane doe xxx-xxx-xxxx TOP 12347 0 rob roy xxx-xxx-xxxx BALLOON 12348 0 paul smith xxx-xxx-xxxx NORTH 

And I try to avoid:

 TABLE AFTER JOIN acctnum sub fname lname phone division 12345 1 john doe xxx-xxx-xxxx EAST 12345 1 john doe xxx-xxx-xxxx WEST 12345 1 john doe xxx-xxx-xxxx NORTH 12346 0 jane doe xxx-xxx-xxxx TOP 12346 0 jane doe xxx-xxx-xxxx BOTTOM 12347 0 rob roy xxx-xxx-xxxx BALOON 12348 0 paul smith xxx-xxx-xxxx NORTH 

So, I decided to go using a union and two conditions. I accept null for conditions when the child account is defined in table 1, but not in table 2, and for everything else I will simply correspond to the minimum.

Answer 1

try using

 SELECT MIN(Table_1.acctnum) as acctnum , MIN(Table_1.sub) as sub,MIN( Table_1.fname) as fname, MIN(Table_1.lname) as name, MIN(Table_1.phone) as phone, MIN(Table_2.division) as division FROM Table_1 INNER JOIN Table_2 ON Table_1.acctnum = Table_2.acctnum AND Table_1.sub = Table_2.sub where Table_1.sub>0 group by Table_1.acctnum union SELECT MIN(Table_1.acctnum) as acctnum , MIN(Table_1.sub) as sub,MIN( Table_1.fname) as fname, MIN(Table_1.lname) as name, MIN(Table_1.phone) as phone, MIN(Table_2.division) as division FROM Table_1 INNER JOIN Table_2 ON Table_1.acctnum = Table_2.acctnum where Table_1.sub=0 group by Table_1.acctnum 

this is the result

 12345 1 john doe xxxxxxxxxx EAST 12346 0 jane doe xxxxxxxxxx BOTTOM 12347 0 rob roy xxxxxxxxxx BALLOON 12348 0 paul smith xxxxxxxxxx NORTH 

if you change min to max TOP, BOTTOM will be entered in the second line

Answer 2

If I understand correctly, it looks like you are trying to join the sub column if it matches. If there is no match on sub , you want it to select the “first” line for this acctnum . Is it correct?

If this is the case, you need to leave the union in complete coincidence, and then perform another left join in the select statement, which defines a division that matches the smallest sub value for this acctnum . The row_number() function can help you with this, for example:

 select t1.acctnum, t1.sub, t1.fname, t1.lname, t1.phone, isnull(t2_match.division, t2_first.division) as division from table1 t1 left join table2 t2_match on t2_match.acctnum = t1.acctnum and t2_match.sub = t1.sub left join ( select acctnum, sub, division, row_number() over (partition by acctnum order by sub) as rownum from table2 ) t2_first on t2_first.acctnum = t1.acctnum 

EDIT

If you do not care about which record you will return from table 2, if the corresponding sub file does not exist, you can combine two different queries (one of which corresponds to a sub-item, and one - only min or max division) with a union .

 select t1.acctnum, t1.sub, t1.fname, t1.lname, t1.phone, t2.division from table1 t1 join table2 t2 on t2.acctnum = t1.acctnum and t2.sub = t1.sub union select t1.acctnum, t1.sub, t1.fname, t1.lname, t1.phone, min(t2.division) from table1 t1 join table2 t2 on t2.acctnum = t1.acctnum left join table2 t2_match on t2_match.acctnum = t1.acctnum and t2_match.sub = t1.sub where t2_match.acctnum is null 

Personally, I don't find union syntax more attractive, and now you need to maintain the request in two places. For this reason, I approve of the row_number() approach.

Answer 3

This will give the desired result accurately (for the data shown):

Updated to not assume that there is always a sub == 1 value:

 SELECT T1.acctnum, T1.sub, T1.fname, T1.lname, T1.phone, T2.division FROM TABLE_1 T1 LEFT JOIN TABLE_2 T2 ON T1.acctnum = T2.acctnum AND T2.sub = (SELECT MIN(T3.sub) FROM TABLE_2 T3 WHERE T1.acctnum = T3.acctnum) ORDER BY T1.lname, T1.fname, T1.acctnum 

Answer 4

It may also work for you:

 SELECT t1.acctnum, t1.sub, t1.fname, t1.lname, t1.phone, ISNULL(MAX(t2.division),MAX(t3.division)) as division FROM table_1 t1 LEFT JOIN table_2 t2 ON (t2.acctnum = t1.acctnum AND t1.sub = t2.sub) LEFT JOIN table_2 t3 ON (t3.acctnum = t1.acctnum) GROUP BY t1.acctnum, t1.sub, t1.fname, t1.lname, t1.phone