Match records on different columns

Question

I have to match a table of Agents to a table Clients based on a varying number of matching columns, some match on all columns, others only match on one, or two or three; and not the same columns. Examples:

Clients table has columns

ClientID, System, Country, Office

Example data:

01   ABC   US   CT
02   ABC   US   RI
03   ABC   US   MA
04   DEF   US   AZ
04   GHI   CA   PQ
05   ABC   ZZ   ZZ

Agents table has columns

AgentID, System, Country, Office

Example data:

A1   ABC   US   CT
A2   ABC        RI
A3   ABC   US   MA
B1   DEF   US   
B2   GHI

Problem: All Agents have a System value, but some Agents have no values for Country or Office, some have only System, some have System and Country, some have System and Office, and the rest have all 3: System, Country and Office. If a Client only matches 1 or 2 fields to an Agent, they're treated like "wildcards" and are considered a match for the appropriate Agent.

How can I create a View that matches every Client and Agent? And shows any Clients that have no corresponding Agent, such as Client 05 above?

So far all I've been able to come up with is to create some kind of table and append the records in 3 or 4 steps, but that is so messy and clumsy, and adds another table to the mix.

I tried a COALESCE statement, and joining with a LIKE statement and surrounding fields with %, but nothing I've tried gives valid results.

Would this call for a Temp table or a CTE? Or something? I'm stuck!

Any ideas?

score 2 · Answer 1 · answered Jun 08 '20 at 23:14

2

I think you want the best match, with NULL values acting as wildcards. If so, then apply is the way to go:

select a.*, a.agentid
from clients c outer apply
     (select top (1) a.*
      from agents a
      where (a.System = c.system or a.System is null) and
            (a.Country = c.Country or a.Country is null) and
            (a.Office = c.Office or a.Office is null)
      order by (case when a.system = c.system then 1 else 0 end) +
               (case when a.Country = c.Country then 1 else 0 end) +
               (case when a.Office = c.Office then 1 else 0 end) desc
     ) a;

answered Jun 08 '20 at 23:14

Gordon Linoff

1,242,037
58
646
786

Good idea, Gordon, I'll try that and see if it works. (Frankly I think the folks at work who are providing this data have a mess on their hands, and I have no idea how they came up with this, it seems hacky!) – Brian Battles Jun 09 '20 at 14:00
@BrianBattles . . . This sort of "defaulting values" type of data is not that unusual. – Gordon Linoff Jun 09 '20 at 14:04

score 1 · Answer 2 · answered Jun 08 '20 at 21:31

1

I think that you want:

create view v_clients_agents as
select c.*, a.agentID
from clients c
left join agents a 
    on a.system = c.system
    and (a.country is null or a.country = c.country)
    and (a.office  is null or a.office  = c.office)

This attempts to join both tables on system, country and office, while allowing null values in agents(country, office). When the join did not find a match, column agentID is null in the resultset.

Note that, depending on your data, this technique might lead to duplicate clients rows in the resultset.

answered Jun 08 '20 at 21:31

GMB

216,147
25
84
135

The problem with this is that if Agent A2 is supposed to be linked to all Clients in System ABC and Office RI no matter what their Country, and Agent B3 is supposed to be linked to all Clients in System DEF with no Office but Country US.... They data isn't consistent. The only thing that would make sense to me is to have all possible valid permutations of the Client values in the Agents table, but they don't want to create and maintain all those hundreds of extra entries here. – Brian Battles Jun 08 '20 at 22:04

Match records on different columns

2 Answers2