CODEWITHSUNDEEP
sqlpostgresqljoin
S. Moco
S. Moco

Reputation: 23

Join two tables on two dimensions without cross-joining

I have a table with IDs and domains(T1). Another with Names and domains(T2). A third with names and IDs (T3).

In its simplified form, my query goes as follows : 

SELECT *
FROM T2
LEFT JOIN T1 
ON T2.domain = T1.domain 
) 
LEFT JOIN T3
ON T1.name = T3.name

The output I'm looking for is a list with columns : "ID", "Name" and "Domain" where either domains or Names match in order to get the IDs. The challenge I face is that one domain can match with two names, and this creates a set of false positives (because the name matches, the wrong ID is also attributed).

Any best practices I should follow when doing these kind of joins would be most helpful.

Thanks S

Upvotes: 1

Views: 133

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I think you want:

SELECT t2.name, t2.domain, coalesce(t1.id, t2.id)
FROM T2 LEFT JOIN
     T1 
     ON T2.domain = T1.domain LEFT JOIN
     T3
     ON T2.name = T3.name AND
        t1.domain IS NULL;  -- no match on T1

This matches on domain first. Then if there is no domain-match, it uses name.

Upvotes: 1

Related Questions