user2498747 user2498747 - 2 months ago 14
MySQL Question

Replacing column name in select with subselect

In my mysql database, I have two tables, project and sub_project.
Subproject contains an id and an acronym; subproject has a project_id which tells which main project it belongs to. The database looks like this:

mysql> SELECT id, acronym FROM project;
+----+---------+
| id | acronym |
+----+---------+
| 14 | P1 |
| 15 | P2 |
| 16 | P3 |
+----+---------+
3 rows in set (0.00 sec)

mysql> SELECT id, project_id FROM subproject;
+----+------------+
| id | project_id |
+----+------------+
| 1 | 14 |
| 2 | 14 |
+----+------------+
2 rows in set (0.00 sec)


I want to make a query that displays the id and acronym of each project, along with the number of subprojects, like so:

SELECT id, acronym, COUNT(select * FROM sub_project WHERE project_id=id) AS 'sub_proj_count'
FROM project;
+----+---------+----------------+
| id | acronym | sub_proj_count |
+----+---------+----------------+
| 14 | P1 | 2 |
| 15 | P2 | 0 |
| 16 | P3 | 0 |
+----+---------+----------------+


However, this is syntactically incorrect. How should this be formulated?

Answer

Schema:

create table project
(   id int not null,
    acronym varchar(100) not null
);
insert project values
(14,'P1'),
(15,'P2'),
(16,'P3');

create table subproject
(   id int not null,
    project_id int not null
);
insert subproject values
(1,14),
(2,14);

Projects that have a subproject:

select p.id,p.acronym,count(s.id) as theCount 
from project p 
join subproject s 
on s.project_id=p.id 
group by p.id,p.acronym; 

+----+---------+----------+
| id | acronym | theCount |
+----+---------+----------+
| 14 | P1      |        2 |
+----+---------+----------+

All Projects even those with no subprojects:

select p.id,p.acronym,count(s.id) as theCount 
from project p 
left join subproject s 
on s.project_id=p.id 
group by p.id,p.acronym; 

+----+---------+----------+
| id | acronym | theCount |
+----+---------+----------+
| 14 | P1      |        2 |
| 15 | P2      |        0 |
| 16 | P3      |        0 |
+----+---------+----------+
Comments