CODEWITHSUNDEEP
postgresql
nimgwfc
nimgwfc

Reputation: 1509

Postgres join when only one row is equal

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?

Upvotes: 0

Views: 464

Answers (1)

Bohemian
Bohemian

Reputation: 425248

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.

Upvotes: 1

Related Questions