CODEWITHSUNDEEP
mysqlsqljoinleft-join
joseph
joseph

Reputation: 53

MySQL join for two tables based on on column and excluding the values of another column

I have two tables.

  1. Project table
  2. Project relationship table

Project table contains all the project IDs and name

Id:1, name:project1
Id:2, name:project2
Id:3, name:project3
Id:4, name:project4
Id:5, name:project5
Id:6, name:project6

Project relationship table contains parent-child relationship between all the project.

parent:1, child:2
parent:1, child:3
parent:4, child:5

Here parent and child refers to project IDs. I want a join query to get the list of only parent project along with their child-project count.

Like this

Id:1, name:project1, count:2
Id:4, name:project4, count:1
Id:6, name:project6, count:0

Upvotes: 0

Views: 126

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If I understand correctly, this should do what you want assuming you do not have relationships nested more than one deep:

select coalesce(pr.parent, p.id), count(pr.child)
from project p left join
     projectrelationship pr
     on pr.child = p.id
group by coalesce(pr.parent, p.id);

The join is to the child in the relationship. This gives two cases:

  • If there are children, then pr.parent is the parent id.
  • If there are no children, then the left join has no match and p.id is the parent id.

The group by then aggregates by the parent and counts the children from pr.

Upvotes: 1

Uueerdo
Uueerdo

Reputation: 15941

This will count the child for projects with no parents.

SELECT p.id, p.name,
       COUNT(prC.child) AS childrenCount
FROM projects AS p
LEFT JOIN project_relations AS prC on p.id = prC.parent -- relations to children
LEFT JOIN project_relations AS prP ON p.id = prP.child -- relations to parent
WHERE prP.parent IS NULL -- no relation to a parent found
GROUP BY p.id, p.name

Upvotes: 1

Related Questions