Madvan Madvan - 4 months ago 7
SQL Question

SQL request with JOIN and COUNT

I can't make a database query, as in this picture:

enter image description here

Here is the database schema:

CREATE Table Ages (
id integer primary key,
Period varchar(10)
);
INSERT INTO Ages VALUES (1, '0-1'), (2, '1-2'), (3, '2-3');
CREATE TABLE Toys (
id integer primary key,
age_id integer foreign key references Ages (id)
);
INSERT INTO Toys VALUES (1, 1), (2, 2), (3, 1), (4, 1);
CREATE TABLE ToysUsers (
id integer primary key,
uid integer,
tid integer foreign key references Toys (id)
);
INSERT INTO ToysUsers VALUES (1,1,1);


I am trying to create the
Result
view from the picture with something like this:

SELECT
toys_age_groups.id, toys_age_groups.period,
COUNT(t3.age_id) as co
FROM
toys_age_groups
LEFT OUTER JOIN
(SELECT
t1.id, t1.age_id
FROM
`toys_toys` t1
LEFT JOIN
toys_user_toys t2 ON t2.tid = t1.id
WHERE
t2.id = ?) AS t3 ON toys_age_groups.id = t3.age_id
GROUP BY
period
ORDER BY
id ASC

Answer

If I am reading your question right, the result table give you the age ID and Period, and then the count of toys per age ID and Period. Here is how you would write that query:

SELECT Ages.ID, Ages.Period, IFNULL(sub.cnt,0) AS Count
FROM Ages LEFT JOIN
(SELECT Toys.age_id, COUNT(*) AS cnt
FROM Toys
GROUP BY Toys.age_id) sub
ON Ages.ID = sub.age_id
Comments