CODEWITHSUNDEEP
mysqlsqlgroup-bycountleft-join
7even
7even

Reputation: 33

Combine rows/columns from two tables - JOIN clause

In the link see my SQLFIDDLE and see b

CREATE TABLE Projects
    (`p_id` int, `project_title` varchar(9), `l_id` int);
INSERT INTO Projects
    (`p_id`, `project_title`, `l_id`)
VALUES
    (1, 'A', 6),
    (2, 'B', 6),
    (3, 'C', 7),
    (4, 'D', 8),
    (5, 'E', 9),
    (6, 'F', 10);
CREATE TABLE Locations
    (`l_id` int, `title` varchar(9), `parent_id` int );
INSERT INTO Locations
    (`l_id`, `title`, `parent_id`)
VALUES
    (1, 'Country', 0),
    (2, 'District1', 1),
    (3, 'District2', 1),
    (4, 'District3', 1),
    (5, 'District4', 1),
    (6, 'Loc 5', 2),
    (7, 'Loc 6', 3),
    (8, 'Loc 7', 3),
    (9, 'Loc 8', 4),
    (10, 'Loc 9', 4),
    (11, 'Loc 10', 4),
    (12, 'Loc 11', 5);

I would like to achieve this:

+------+-----------+-------------+
| L_ID | Title     | count P_ID  | 
+------+-----------+-------------+
|  2   | District1 |    2        | 
|  3   | District2 |    2        | 
|  4   | District3 |    2        | 
|  5   | District4 |    0        | 
+----+------------+------+-------+

I have tried with INNER JOIN, LEFT OUTER JOIN. All i can achieve is like below and doesnt help me:

    +------+-----------+----------------------+
    | L_ID | Title     | parent_id  | counted |
    +------+-----------+------------+---------+
    |  6   | Loc 5     |    2       |   2     | 
    |  7   | Loc 6     |    3       |   2     | 
    |  9   | Loc 8     |    4       |   2     | 
    +---- -+-----------+------------+---------+

Locations table is a nested one, if this matters. I need to count projects that are in each District and also to get District name.

I tried:

SELECT l.*, COUNT(p.l_id) AS thecounted 
FROM locations l 
INNER JOIN  projects p  ON p.l_id = l.l_id  
GROUP BY l.parent_id

and

SELECT l.*, COUNT(p.l_id) AS thecounted 
FROM locations l 
LEFT OUTER JOIN projects p on l.l_id = p.l_id 
GROUP BY l.parent_id

Upvotes: 0

Views: 47

Answers (2)

Martin P
Martin P

Reputation: 3

The solution of GMB returns this 1 row

l_id    title       count_p_id
1       Country     0

using this script version

select d.l_id, d.title, count(p.l_id) count_p_id
from locations d
left join locations l on l.parent_id = d.l_id
left join projects  p on p.l_id      = l.l_id
where d.parent_id = 0
group by d.l_id, d.title

We get the desired result with the slightly corrected condition

where d.parent_id = 1

Result:

l_id    title       count_p_id
2       District1   2
3       District2   2
4       District3   2
5       District4   0

Sorry for posting the answer, instead of a simple comment, which would be sufficient, but don't have enough reputation credits yet.

Upvotes: 0

GMB
GMB

Reputation: 222432

Consider two joins:

select d.l_id, d.title, count(p.l_id) count_p_id
from locations d
left join locations l on l.parent_id = d.l_id
left join projects  p on p.l_id      = l.l_id
where d.parent_id = 0
group by d.l_id, d.title

The query starts from the list of districts (d), whose parent is 0. Then, it goes down one level to the locations (l), and looks up the corresponding projects (p). The final step is aggregation.

Upvotes: 1

Related Questions