Search code examples
mysqllinqselectrowhierarchy

Mysql/Linq Get rows with no repeated values without using DISTINCT

imagine several tables in a database with some fields:

Table "Builder": ID_BUILDER, other fields...

Table "Project": ID_PROJECT, ID_BUILDER, ID_RESOURCE, other fields...

Table "Resources": ID_RESOURCE, ID_BUILDER, other fields...

My question is:

How can I get the ID_BUILDER and the Name of that builder that has made just one project (no more) and has used a resource that has not been used by any other? An example that can clarify it could be:

Table Builder |ID_BUILDER| NAME |

|----------|--------------|

|1 | GustavEiffel |

|2 | Egypcians |

|3 | Me |

Table Projects |ID_PROJECT| NAME | ID_BUILDER|

|----------|------------|-----------|

|1 | EiffelTower| 1 |

|2 | LionBelfort| 1 |

|3 | Pyramids | 2 |

|4 | PaperFlower| 3 |

Table Resources |ID_RESOURCE| NAME | ID_PROJECTS|

|-----------|------|------------|

|1 | Steel| 1 |

|2 | Stone| 2 |

|2 | Stone| 3 |

|3 | Paper| 4 |

The query would ask for the ID of a builder that has just done one project and the answer should be {3, Me} because Me just has made 1 project and has used just 1 resource not used by any other (Gustav Eiffel has made 2 projects and Egipcians has used stone in their only project Pyramids, but stone has been also been used for LionBelfort project)

I have been trying to use:

But I couldn't get an aswer. Any help please?

Solution

  • This query contains column which counts how many times resource was used in different projects:

    select p.id_builder, p.name, r.id_resource, 
       count(1) over (partition by id_resource) cnt_resources
from Resources r join Projects p on p.id_project = r.id_projects

    You can join this subquery with classical group-by-having approach and take only rows which have count_resources=1:

    select p.id_builder, b.name
from Projects p 
join Builder b on b.id_builder = p.id_builder
join  (select p.id_builder, r.id_resource, 
              count(1) over (partition by id_resource) cnt_resources
       from Resources r join Projects p on p.id_project = r.id_projects) c
  on p.id_builder = c.id_builder and cnt_resources = 1
group by p.id_builder, b.name
having count(1) = 1

    dbfiddle demo