Postgres join when only one row is equal-CodePudding

I have two tables and I am wanting to do an inner join between table_1 and table_2 but only when there is one row in table_2 that meets the join criteria.

For example:

table_1

 id              | name             | age          |           
----------------- ------------------ --------------   
 1               | john jones       | 10           |
 2               | pete smith       | 15           | 
 3               | mary lewis       | 12           | 
 4               | amy roberts      | 13           |

table_2

 id              | name             | age          | hair         | height       |           
----------------- ------------------ -------------- -------------- --------------   
 1               | john jones       | 10           | brown        | 100          |
 2               | john jones       | 10           | blonde       | 132          | 
 3               | mary lewis       | 12           | brown        | 146          | 
 4               | pete smith       | 15           | black        | 171          |

So I want to do a join when name is equal, but only when there is one corresponding matching name in table_2

So my results would look like this:

 id              | name             | age          |  hair        |        
----------------- ------------------ -------------- --------------   
 2               | pete smith       | 15           | black        | 
 3               | mary lewis       | 12           | brown        |

As you can see, John Jones isn't in the results as there are two corresponding rows in table_2.

My initial code looks like this:

select tb.id,tb.name,tb.age,sc.hair
from table_1 tb
inner join table_2 sc
on tb.name = sc.name and tb.age = sc.age

Can I apply a clause within the join so that it only joins on rows which are unique matches?

CodePudding user response：

The short answer is - no. A join clause evaluates conditions for each potential pair of rows that might be joined together, and then returns true or false.

You could consider using "group" and "having" to determine the unique names and ages that appear exactly once in the second table. Something like:

select *
FROM table_1
INNER JOIN table_2
  ON table_1.name = table_2.name AND table_1.age = table_2.age
WHERE (table_2.name, table_2.age) IN (SELECT name, age FROM table_2 GROUP BY name,age HAVING COUNT(*) = 1)

CodePudding user response：

Group by all columns and apply having count(*) = 1

select tb.id,tb.name,tb.age,sc.hair
from table_1 tb
join table_2 sc
  on tb.name = sc.name and tb.age = sc.age
group by tb.id,tb.name,tb.age,sc.hair
having count(*) = 1

The interesting thing to note is that you don’t need the aggregate expression (in the case count(*) )in the select clause.