UPD removed one unessential line.

On python I use sqlite3 to work with tables. I am trying to combine data from 3 tables and count the number of rows of statistics in combination of data from the first two:

SELECT pd.d_id, pd.l_id, COUNT(*) FROM ( SELECT p.id AS l_id, d.id AS d_id FROM people p LEFT JOIN duties d ON p.project_id = d.project_id) pd LEFT JOIN stats st ON pd.l_id = st.login_id AND pd.d_id = st.duty_id GROUP BY pd.d_id, pd.l_id;

At the same time, I see that COUNT correctly counts the number of rows, BUT it does not display the number = 0, if it is, that is, it displays only what it is. It seems like LEFT JOIN should take that into account.

I need:

 pd.d_id | pd.l_id | COUNT(*) 1 | 12 | 1 1 | 11 | 1 1 | 10 | 0 2 | 12 | 2 2 | 11 | 0 2 | 10 | 4 ...

Output to me:

 pd.d_id | pd.l_id | COUNT(*) 1 | 12 | 1 1 | 11 | 1 2 | 12 | 2 2 | 10 | 4 ...

what am i doing wrong?

  • Interesting, but without WHERE what will happen? As I understand, the st.project_id in the strings of interest will be NULL - Smolyanoye Yuchel
  • With what data does the query return such a result? - 2SRTVF
  • what am i doing wrong? As the leftmost table of the main query, there should be a table or a subquery that returns all possible value pairs (people.id, duties.id) . Something like SELECT DISTINCT p.id, d.id FROM people p, dutied d . Then all of them will be present in the output set - incl. and with zeros. - Akina
  • one
    I do not believe. Create a fiddle or at least a set of scripts reproducing the problem. - Akina
  • one
    @NovitskiyDenis It is needed to always have a combination, the amount of data for which is considered. But it can be removed in favor of the same LEFT JOIN, I agree. - user3522383

1 answer 1

Oh, friends. My cant. He knew that the problem is something simple. In general, I counted on the whole line, and not on the part that should be missing, as a result, in those places where there should have been 0, there was 1.

What I saw yesterday and how I thought the columns would not understand. I tried a lot of things, apparently messed up in my head.

As a result, such a thing has earned:

 SELECT pd.d_id, pd.l_id, COUNT(st.login_id) FROM ( SELECT p.id AS l_id, d.id AS d_id FROM people p LEFT JOIN duties d ON p.project_id = d.project_id) pd LEFT JOIN stats st ON pd.l_id = st.login_id AND pd.d_id = st.duty_id GROUP BY pd.d_id, pd.l_id;

Izi @Akina Thank you for instilling confidence in the fact that this cannot be done (I thought so myself, but anything can happen), and it seemed that somewhere in the sqlite bug itself ...