SQLite Forum

LEFT JOIN Misunderstanding
Login

LEFT JOIN Misunderstanding

(1) By jose isaias cabrera (jicman) on 2021-07-30 02:35:26 [link] [source]

Greetings.

The following code is working partially:

WITH LastEntries (ProjID, pl_insert, pe_insert)
AS
(
  SELECT projid,
         max(InsertDate),
         (SELECT max(insertdate) FROM project_extras where projid = e.projid)
    FROM project_list e
    GROUP BY projid
 )
SELECT a.ProjID, a.Project_Name, count(c.CreatedDate), a.Manager
FROM Project_List AS a 
LEFT JOIN Project_Extras AS b
LEFT JOIN Project_Highlights AS c
LEFT JOIN LastEntries AS d
WHERE
    c.CreatedDate LIKE '2021-07-%' 
AND
    b.MonthlyRpt = 'y'
AND
    b.BudgetYear = '2021'
AND
    a.InsertDate = d.pl_insert
AND
    b.InsertDate = d.pe_insert
AND a.ProjID = b.ProjID 
AND a.ProjID IS c.ProjID
AND a.ProjID = d.projid
GROUP BY a.ProjID
ORDER BY a.Manager, a.ProjID
;

But I am expecting 108 records, and I am only getting 13. The reason is that the Project_Highlights table does not have all the ProjIDs that Project_List has. However, I am trying to show those ProjIDs in Project_List also with a count of 0. I know what I am missing is simple, but, I have searched a bunch of places trying to understand the reasoning behind it. I know it's because c.ProjID is NULL, but I am missing the way to have it worked. Any input would be greatly appreciated. Thanks.

(2.1) By Rico Mariani (rmariani) on 2021-07-30 05:12:17 edited from 2.0 in reply to 1 [link] [source]

Perhaps this post is relevant

(3) By jose isaias cabrera (jicman) on 2021-07-30 12:04:26 in reply to 2.1 [link] [source]

Thanks, Rico, but I don't think this is the problem. SQLite v3.35.2 does the same thing. Thanks.

(5.1) By Rico Mariani (rmariani) on 2021-07-30 12:53:34 edited from 5.0 in reply to 3 [link] [source]

My derp... I didn't notice they were all in the WHERE clause...

Once you move the join condition to the ON clause if there are still too few rows THEN the optimization might be biting you.

(4) By anonymous on 2021-07-30 12:28:46 in reply to 1 [link] [source]

You need to move the join conditions from the WHERE clause to ON clauses in each JOIN. Having them in the WHERE clause is equivalent only for inner joins.

    WITH LastEntries (ProjID, pl_insert, pe_insert)
    AS
    (
      SELECT projid,
             max(InsertDate),
             (SELECT max(insertdate) FROM project_extras where projid = e.projid)
        FROM project_list e
        GROUP BY projid
     )
    SELECT a.ProjID, a.Project_Name, count(c.CreatedDate), a.Manager
    FROM Project_List AS a
    LEFT JOIN Project_Extras AS b
        ON a.ProjID = b.ProjID
    LEFT JOIN Project_Highlights AS c
        ON a.ProjID IS c.ProjID
    LEFT JOIN LastEntries AS d
        ON a.ProjID = d.projid
    WHERE
        c.CreatedDate LIKE '2021-07-%' 
    AND
        b.MonthlyRpt = 'y'
    AND
        b.BudgetYear = '2021'
    AND
        a.InsertDate = d.pl_insert
    AND
        b.InsertDate = d.pe_insert
    GROUP BY a.ProjID
    ORDER BY a.Manager, a.ProjID
    ;

(6) By Rico Mariani (rmariani) on 2021-07-30 12:57:40 in reply to 4 [link] [source]

The above is of course totally right, I think it didn't go far enough.

    WITH LastEntries (ProjID, pl_insert, pe_insert)
    AS
    (
      SELECT
        projid,
        max(InsertDate),
        (SELECT max(insertdate) FROM project_extras where projid = e.projid)
        FROM project_list e
        GROUP BY projid
     )
    SELECT a.ProjID, a.Project_Name, count(c.CreatedDate), a.Manager
    FROM Project_List AS a
    LEFT JOIN Project_Extras AS b
        ON a.ProjID = b.ProjID
        AND b.MonthlyRpt = 'y'
        AND b.BudgetYear = '2021'
    LEFT JOIN Project_Highlights AS c
        ON a.ProjID IS c.ProjID
        AND c.CreatedDate LIKE '2021-07-%' 
    LEFT JOIN LastEntries AS d
        ON a.ProjID = d.projid
        AND a.InsertDate = d.pl_insert
        AND b.InsertDate = d.pe_insert
    GROUP BY a.ProjID
    ORDER BY a.Manager, a.ProjID;

(7) By ddevienne on 2021-07-30 13:27:12 in reply to 6 [link] [source]

FWIW, I much prefer https://sqlite.org/forum/forumpost/bd05c5ea6ee5fb91?t=h,
i.e. ON should only be with columns from the 2 joined tables on both sides,
and WHERE with only a single column from any table and a literal (typically).

The way you mix ON-predicates and WHERE-predicates in ON now,
or everything in WHERE before, confuses things IMHO (and was wrong for the latter).

It's partly a matter of style.
I like to think of it as ON being about stitching tables together, and WHERE about filtering.
The former is also a filtering of sort, yet different in my mind. YMMV :)

(10) By jose isaias cabrera (jicman) on 2021-07-30 13:42:38 in reply to 7 [link] [source]

The one you refer still gives me the same result as the original. The second one gives me more, but I can see that I still need to filter a few more things.

(12.1) By Rico Mariani (rmariani) on 2021-07-30 17:23:51 edited from 12.0 in reply to 7 [link] [source]

It's not a style choice at all really.

If you have

   X LEFT Y ON Z
   WHERE W

If the conditions are in the W clause they will reduce the number of rows returned from X.

If the condition is on the Z clause then all the rows of X will be returned even if some of the Z conditions fail. You'll get nulls.

So the W doesn't participate in the "leftness" of the join.

In this particular case there were 4 joins and all of the X were desired regardless of the join conditions, so all the conditions must go in the ON.

ON is equivalent to WHERE only for INNER joins.

(9) By jose isaias cabrera (jicman) on 2021-07-30 13:35:06 in reply to 6 [link] [source]

This one works. Thanks so much. So, group the conditions with the ON. Learned something new. Again. :-) Thanks.

(13) By Rico Mariani (rmariani) on 2021-07-30 18:13:22 in reply to 9 [link] [source]

only the conditions that are in the ON participate in the LEFTness of the join.

(8) By jose isaias cabrera (jicman) on 2021-07-30 13:33:53 in reply to 4 [link] [source]

This one still gives me the same result...

(11) By jose isaias cabrera (jicman) on 2021-07-30 16:13:48 in reply to 1 [link] [source]

This is for my own search in the future, this works:

WITH LastEntries (ProjID, pl_insert, pe_insert)
AS
(
  SELECT projid,
         max(InsertDate),
         (SELECT max(insertdate) FROM project_extras where projid = e.projid)
    FROM project_list e
    GROUP BY projid
 )
SELECT count(c.CreatedDate), a.Status, a.ProjID, a.Project_Name,  a.Manager
FROM Project_List AS a 
LEFT JOIN Project_Extras AS b
ON a.ProjID == b.ProjID
AND
    b.MonthlyRpt = 'y'
AND
    b.BudgetYear = '2021'
LEFT JOIN Project_Highlights AS c
ON b.ProjID = c.ProjID
AND 
	c.CreatedDate LIKE '2021-07-%' 
LEFT JOIN LastEntries AS d
WHERE
	a.Status == 'Acknowledged'
AND
    a.InsertDate = d.pl_insert
AND
    b.InsertDate = d.pe_insert
AND
	a.ProjID = d.projid
GROUP BY a.ProjID
ORDER BY a.Manager, a.ProjID
;

thanks for all the help.

josé

(14) By Rico Mariani (rmariani) on 2021-08-03 01:43:20 in reply to 11 [link] [source]

The extra constraints in the WHERE will affect rows returned by this query. That may be ok for you but it makes the joins not purely left.

(15) By jose isaias cabrera (jicman) on 2021-08-04 15:32:54 in reply to 14 [link] [source]

Hi Rico.

The extra constraints in the WHERE will affect rows returned by this query. That may be ok for you but it makes the joins not purely left.

I don't understand. Where can those constraints go? That is the only spot where they can be place, correct?

(16) By Deon Brewis (deonb) on 2021-08-04 16:36:45 in reply to 15 [link] [source]

A LEFT JOIN without an ON clause is not a LEFT JOIN, it's an INNER JOIN.

It could be what you want, but then you should write it as an INNER JOIN, otherwise you'll really confuse the next person (or just yourself) looking back to it in a few years.

You shouldn't do "INNER JOIN" + WHERE as a habit either, but at least it will work. "LEFT JOIN" + WHERE doesn't actually work.

(18) By Keith Medcalf (kmedcalf) on 2021-08-04 19:51:47 in reply to 16 [link] [source]

"INNER JOIN" + WHERE is how one does an equijoin. It works, it has always worked, and there is no other alternate specification.

"LEFT JOIN" + WHERE does actually work and do EXACTLY what you specified by done. It is true that the partial descent specifier must be specified in the ON clause (in these modern times -- generally because the kidz were otherwise confused) for the outer join case and that the WHERE clause only applies to the final projection, but this is as things have been since the beginning times.

The real problem is that many people "say what they habit" rather than "say what they mean". They are in love with "LEFT JOIN" because they heard it once and thought it was cool, so now they use it ALL THE TIME, even when it does not apply, because, OH BOY does it sound COOL!

This is the problem with children today. They saw it on TV once and without actually understanding what they are doing, just try to copy "the cool kidz", because, hey, its so cool!

(19) By Rico Mariani (rmariani) on 2021-08-04 19:54:57 in reply to 18 [link] [source]

What he said :D

(20) By Larry Brasfield (larrybr) on 2021-08-04 20:01:26 in reply to 18 [link] [source]

The real problem is that many people "say what they habit" rather than "say what they mean". They are in love with "LEFT JOIN" because they heard it once and thought it was cool, so now they use it ALL THE TIME, even when it does not apply, because, OH BOY does it sound COOL!

Without contending that coolness is never a factor, I am sure I speak for more than a few SQL novices by saying: Sometimes, SQL constructs are used as a form of incantation, preferred because they are thought to summon satisfactory results rather than for their cachet.

(21) By Rico Mariani (rmariani) on 2021-08-04 20:07:13 in reply to 20 [link] [source]

I definitely see a lot of "sql incantations" but that often means the incantor doesn't know what's actually going on. This seems like a good place to help fix that.

(22.1) By Deon Brewis (deonb) on 2021-08-04 20:40:07 edited from 22.0 in reply to 18 [link] [source]

> "INNER JOIN" + WHERE is how one does an equijoin. It works, it has always worked, and there is no other alternate specification."

ANSI might have something to say about that. FROM ISO/IEC 9075. 

Section 7 (Query Expressions), Subsection 7.7:

<joined table> ::=
  <cross join>
  | <qualified join>
  | <natural join>

<cross join> ::=
  <table reference> CROSS JOIN <table factor>

<qualified join> ::=
  { <table reference> | <partitioned join table> }
  [ <join type> ] JOIN
  { <table reference> | <partitioned join table> }
  <join specification>

<partitioned join table> ::=
  <table factor> PARTITION BY
     <partitioned join column reference list>

<partitioned join column reference list> ::=
 <left paren> <partitioned join column reference>
  [ { <comma> <partitioned join column reference> }... ]
   <right paren>

<partitioned join column reference> ::=
 <column reference>

<natural join> ::=
 { <table reference> | <partitioned join table> }
  NATURAL [ <join type> ] JOIN
   { <table factor> | <partitioned join table> }

<join specification> ::=
  <join condition>
  | <named columns join>

<join condition> ::=
  ON <search condition>

<named columns join> ::=
  USING <left paren> <join column list> <right paren>

<join type> ::=
  INNER
  | <outer join type> [ OUTER ]

<outer join type> ::=
  LEFT
  | RIGHT
  | FULL

<join column list> ::=
  <column name list>

(23) By Rico Mariani (rmariani) on 2021-08-04 21:07:33 in reply to 22.1 [link] [source]

I don't understand. That's grammar. ANSI must say somewhere what INNER JOIN + ON means or what LEFT OUTER JOIN + ON means and how it relates to where but the grammar is silent on this. It's just grammar.

(26) By Deon Brewis (deonb) on 2021-08-04 23:11:19 in reply to 23 [link] [source]

I just meant that "WHERE" is not a listed <join condition> for "INNER" according to the standard, only "ON" is.

(34) By Keith Medcalf (kmedcalf) on 2021-08-10 20:48:42 in reply to 26 [link] [source]

That sort of shit happens frequently when you write the design document after the product is complete.

(24) By Keith Medcalf (kmedcalf) on 2021-08-04 22:47:15 in reply to 22.1 [link] [source]

Methinks you know not what an equijoin (inner join) is.

An equijoin is a projection (join) of two tables where some item in one table is equal to some item in another table.

SELECT * FROM A, B WHERE A.X == B.X;

is an equijoin of tables A and B.

An outer join is one where all rows of one (or both) tables are included in the projection (LEFT, RIGHT, or FULL (meaning both)) in the projection and the values projected from the other table are NULL if no match is found.

SELECT * FROM A LEFT OUTER JOIN B ON A.X == B.X;

produces the same projection columns, but includes ALL rows from table A, even those where no matching row exists in table B, by setting all such projection results from table B to NULL in that instant.

SELECT * FROM A LEFT OUTER JOIN B ON A.X == B.X WHERE B.X IS NOT NULL;

is the same as

SELECT * FROM A, B WHERE A.X == B.X;

Pre the invasion of "The Great Unwashed" one would specify

SELECT * FROM A LEFT JOIN B ON A.X == B.X;

Used to be spoken as

SELECT * FROM A, B WHERE A.X *= B.X;

but this was confusing to the children. Hence the SQL syntax was expanded to permit the use of an ON clause because "think of the children" so that the children could better understand OUTER joins. The ON clause is ONLY meaningful in OUTER join contexts. It is useless for INNER joins and serves no useful purpose whatsoever.

(25) By Rico Mariani (rmariani) on 2021-08-04 22:59:06 in reply to 24 [link] [source]

By useless he means you don't get anything new from it. WHERE is the same.

In fairness, the *= syntax was easy to get wrong and it was less flexible than the ON form where the condition can be an arbitrary predicate and not just equality.

I came along just as the ANSI form was gaining favor and I was glad of it. :D

(28) By Deon Brewis (deonb) on 2021-08-04 23:27:33 in reply to 25 [link] [source]

Oh, Hi by the way :)

I think we may have run into each other a few times during the VSIX/Nautilus days.

(39) By Rico Mariani (rmariani) on 2021-08-11 06:06:55 in reply to 28 [link] [source]

:D

(27) By Deon Brewis (deonb) on 2021-08-04 23:18:31 in reply to 24 [link] [source]

"equijoin (inner join)"

An equijoin is not an INNER JOIN. You can do an INNER JOIN ON inequality if you want, and it won't be considered an equijoin.

"It is useless for INNER joins and serves no useful purpose whatsoever."

The usefulness of always using the ANSI syntax is that if you switch your INNER JOIN over to an LEFT JOIN you're not left confused with why it didn't immediately work.

It also serves to document your intent to the next person who reads your code.

(29) By Keith Medcalf (kmedcalf) on 2021-08-05 00:56:39 in reply to 27 [link] [source]

That is true.

An INNER join only includes in the projection results rows which pass all constraints.

An OUTER join includes in the projection results all rows from the specified table in the projection and uses one ALL NULL record for non-matched constraints (LEFT / RIGHT / FULL -- means both).

(31) By doug (doug9forester) on 2021-08-05 20:01:10 in reply to 24 [link] [source]

Keith said: ``` An equijoin is a projection (join) of two tables where some item in one table is equal to some item in another table.

SELECT * FROM A, B WHERE A.X == B.X;

is an equijoin of tables A and B. ``` If the A->B relationship is one-to-many, are multiple rows returned? That is, if A.X matches multiple B.X's, is a row returned for each match? And what about many-to-many? And many-to-one? Without DISTINCT, what happens?

(32) By Keith Medcalf (kmedcalf) on 2021-08-05 20:49:19 in reply to 31 [link] [source]

Yes. And there is no DISTINCT and the projection result is not DISTINCT and may contain duplicate tuples.

(46) By tom (younique) on 2021-08-13 08:42:41 in reply to 22.1 [link] [source]

<outer join type> ::= LEFT | RIGHT | FULL

Maybe this is a good time to renew the request for supporting RIGHT joins. I know that right and left are interchangeable but sometimes it is more intuitive to use the "right" variant.

(17.4) By Rico Mariani (rmariani) on 2021-08-04 20:12:11 edited from 17.3 in reply to 15 [link] [source]

An example is easier than trying to explain it further.

create table T1 (id integer);
create table T2 (id integer, t text);

insert into T1 values (1), (2), (3);

insert into T2 values (2, 'hi'), (3, 'bye');

select '----';
select 'all T1';
select * from T1;

select '----';
select 'T1 inner T2: this is a cross join because no ON clause';
select * from T1 inner join T2;

select '----';
select 'T1 left T2: this is also a cross join because no ON clause';
select * from T1 left join T2;

select '----';
select 'T1 inner T2:  regular inner join, no surprises';
select * from T1 inner join T2 on T1.id = T2.id;

select '----';
select 'T1 left T2:  regular left join, no surprises';
select * from T1 left join T2 on T1.id = T2.id;

select '----';
select 'T1 left T2:  regular left join with where filtering, removes rows from T1';
select * from T1 left join T2 on T1.id = T2.id where T2.t = 'hi';

select '----';
select 'T1 left T2:  regular left join with on filtering, does not remove rows from T1';
select * from T1 left join T2 on T1.id = T2.id and T2.t = 'hi';

Results:

----
all T1
1
2
3
----
T1 inner T2: this is a cross join because no ON clause
1|2|hi
1|3|bye
2|2|hi
2|3|bye
3|2|hi
3|3|bye
----
T1 left T2: this is also a cross join because no ON clause
1|2|hi
1|3|bye
2|2|hi
2|3|bye
3|2|hi
3|3|bye
----
T1 inner T2:  regular inner join, no surprises
2|2|hi
3|3|bye
----
T1 left T2:  regular left join, no surprises
1||
2|2|hi
3|3|bye
----
T1 left T2:  regular left join with where filtering, removes rows from T1
2|2|hi
----
T1 left T2:  regular left join with on filtering, does not remove rows from T1
1||
2|2|hi
3||

The second last query still removes T1 rows, so if you want only conditional columns from the left join all the tests have to be in the ON clause. Note that the last two queries are both reasonable/valid but they are different.

In this simple example the predicate in the where clause of the second last query basically converted the left join to inner. But you could imagine a more subtle predicate that would only remove some of the rows leaving some of the left-ness intact. Basically the WHERE happens after the joins. The ON defines the join criteria. The QP sometimes moves things around if it is safe to do so.

(30) By Gerry Snyder (GSnyder) on 2021-08-05 03:13:54 in reply to 17.4 [link] [source]

Thank you.

The examples are very instructive.

(33) By jose isaias cabrera (jicman) on 2021-08-10 20:26:31 in reply to 30 [link] [source]

I agree and also thank you for the examples.

jos♪

(35) By jose isaias cabrera (jicman) on 2021-08-10 20:52:30 in reply to 17.4 [link] [source]

One last question on this... I have been trying to figure this out for a while, but I can't seem to come up with the correct SQLite code. This code works correctly:

WITH LastEntries (ProjID, pl_insert, pe_insert)
AS
(
  SELECT projid,
         max(InsertDate),
         (SELECT max(insertdate) FROM project_extras where projid = e.projid)
    FROM project_list e
    GROUP BY projid
 )
SELECT count(c.CreatedDate) AS Aug, a.Project_Type , a.ProjID
FROM Project_List AS a 
LEFT JOIN Project_Extras AS b
ON a.ProjID == b.ProjID
AND
    b.FinCarryOver != 'y'
AND
    b.MonthlyRpt = 'y'
AND
    b.BudgetYear = '2021'
LEFT JOIN Project_Highlights AS c
ON b.ProjID = c.ProjID
AND 
    c.CreatedDate LIKE '2021-08%' 
LEFT JOIN LastEntries AS f
WHERE
    a.Project_Delivered != 'Yes'
AND
    a.PMO_Board_Report != 'No' 
AND
    a.Status == 'Acknowledged'
AND
    a.InsertDate = f.pl_insert
AND
    b.InsertDate = f.pe_insert
AND
    a.ProjID = f.projid
GROUP BY a.ProjID
ORDER BY a.Manager, a.ProjID
;

which results to:
2|Activity|PR0000019131
3|Project|PR0000019132
3|Project|PR0000019155
3|Project|PR0000019175
3|Project|PR0000019586
0|Project|PR0000018388
2|Project|PR0000018390
2|Project|PR0000018391
0|Activity|PR0000019764

This is correct.

But this other code gives the wrong result,

WITH LastEntries (ProjID, pl_insert, pe_insert)
AS
(
  SELECT projid,
         max(InsertDate),
         (SELECT max(insertdate) FROM project_extras where projid = e.projid)
    FROM project_list e
    GROUP BY projid
 )
SELECT count(d.CreatedDate) AS Jul, count(c.CreatedDate) AS Aug, a.Project_Type , a.ProjID
FROM Project_List AS a 
LEFT JOIN Project_Extras AS b
ON a.ProjID == b.ProjID
AND
    b.FinCarryOver != 'y'
AND
    b.MonthlyRpt = 'y'
AND
    b.BudgetYear = '2021'
LEFT JOIN Project_Highlights AS c
ON b.ProjID = c.ProjID
AND 
    c.CreatedDate LIKE '2021-08%' 
LEFT JOIN Project_Highlights AS d
ON b.ProjID = d.ProjID
AND 
    d.CreatedDate LIKE '2021-07%' 
LEFT JOIN LastEntries AS f
WHERE
    a.Project_Delivered != 'Yes'
AND
    a.PMO_Board_Report != 'No' 
AND
    a.Status == 'Acknowledged'
AND
    a.InsertDate = f.pl_insert
AND
    b.InsertDate = f.pe_insert
AND
    a.ProjID = f.projid
GROUP BY a.ProjID
ORDER BY a.Manager, a.ProjID
;

which results to:
4|4|Activity|PR0000019131
6|6|Project|PR0000019132
6|6|Project|PR0000019155
6|6|Project|PR0000019175
6|6|Project|PR0000019586
2|0|Project|PR0000018388
4|4|Project|PR0000018390
4|4|Project|PR0000018391
0|0|Activity|PR0000019764

I am expecting:
2|2|Activity|PR0000019131
2|3|Project|PR0000019132
2|3|Project|PR0000019155
2|3|Project|PR0000019175
2|3|Project|PR0000019586
2|0|Project|PR0000018388
2|2|Project|PR0000018390
2|2|Project|PR0000018391
0|0|Activity|PR0000019764

I know it's something simple, again, but I can't seem to put my finger on the problem. Any thoughts? Thanks.

josé

(36) By Rico Mariani (rmariani) on 2021-08-11 03:30:30 in reply to 35 [link] [source]

I didn't look at this in detail but I noticed this:

AND
    a.InsertDate = f.pl_insert
AND
    b.InsertDate = f.pe_insert
AND
    a.ProjID = f.projid

As long as you mention those in the WHERE clause it won't be a real left join. All constraints on F must be in the ON clause.

Now if you really want to filter away rows in A using those booleans that's fine. That's legal and often reasonable but at that point it isn't really a LEFT JOIN anymore. You're effectively inner joining from A to F over projid.

(37) By Rico Mariani (rmariani) on 2021-08-11 06:01:24 in reply to 36 [source]

WITH
LastEntries (ProjID, pl_insert, pe_insert) AS (
 SELECT projid, max(InsertDate), ( SELECT max(insertdate) FROM project_extras WHERE projid = e.projid )
  FROM project_list AS e
  GROUP BY projid)

SELECT count(d.CreatedDate) AS Jul, count(c.CreatedDate) AS Aug, a.Project_Type, a.ProjID
  FROM Project_List AS a
  LEFT JOIN Project_Extras AS b ON a.ProjID = b.ProjID AND b.FinCarryOver <> 'y' AND b.MonthlyRpt = 'y' AND b.BudgetYear = '2021'
  LEFT JOIN Project_Highlights AS c ON b.ProjID = c.ProjID AND c.CreatedDate LIKE '2021-08%'
  LEFT JOIN Project_Highlights AS d ON b.ProjID = d.ProjID AND d.CreatedDate LIKE '2021-07%'
  LEFT JOIN LastEntries AS f
  WHERE a.Project_Delivered <> 'Yes' AND 
        a.PMO_Board_Report <> 'No' AND 
        a.Status = 'Acknowledged' AND 
        a.InsertDate = f.pl_insert AND 
        b.InsertDate = f.pe_insert AND 
        a.ProjID = f.projid
GROUP BY a.ProjID
ORDER BY a.Manager, a.ProjID;

You have

SELECT count(d.CreatedDate) AS Jul, count(c.CreatedDate) AS Aug, a.Project_Type, a.ProjID

But

GROUP BY a.ProjID

That's going to give you weird results. The select line should really be only group by columns or aggregates. Maybe add

GROUP BY a.ProjID, a.Project_Type

And as before

        a.InsertDate = f.pl_insert AND 
        b.InsertDate = f.pe_insert AND 
        a.ProjID = f.projid

is killing the LEFT in LEFT JOIN LastEntries AS f

Those probably need to move into an ON clause for f unless you intended that to be an inner join.

(38) By Rico Mariani (rmariani) on 2021-08-11 06:02:59 in reply to 37 [link] [source]

BTW good indenting reveals the structure of the query. Bad indenting makes it harder to see what goes with what. I would say the original version is bad. Even if you don't like my version I'm sure you can do better. But I wouldn't have spotted the fact that there is no ON for the LEFT on F in your version.

(40) By jose isaias cabrera (jicman) on 2021-08-11 15:02:08 in reply to 38 [link] [source]

Thanks. You're right, but every person's idea of indenting and/or breaking code is different. But, I appreciate and thank you for your input.

josé

(42) By Rico Mariani (rmariani) on 2021-08-11 22:50:40 in reply to 40 [link] [source]

There's lots of choices. If you look at what I did you can see why I might do that. You can do something else you like that highlights which conditions go with which. I'm pretty good at reading this stuff and I found it very hard to follow yours. Adopt what you like but you have room to take advantage of more indenting. You could keep the each ON clause separate but indented. You could break at each AND. There's lots of good choices here but you need to be able to see which predicate goes with which clause at a glance, that helps a lot.

(44) By Rico Mariani (rmariani) on 2021-08-11 22:56:07 in reply to 42 [link] [source]

Here's concrete suggestion (you don't have to go with this one of course).

This is as similar to what you have as possible with a little extra indenting to reveal structure.

WITH LastEntries (ProjID, pl_insert, pe_insert)
AS
(
  SELECT projid,
         max(InsertDate),
         (SELECT max(insertdate) FROM project_extras where projid = e.projid)
    FROM project_list e
    GROUP BY projid
 )
SELECT count(c.CreatedDate) AS Aug, a.Project_Type , a.ProjID
FROM Project_List AS a
  LEFT JOIN Project_Extras AS b
    ON a.ProjID == b.ProjID
    AND
       b.FinCarryOver != 'y'
    AND
       b.MonthlyRpt = 'y'
    AND
       b.BudgetYear = '2021'
  LEFT JOIN Project_Highlights AS c
    ON b.ProjID = c.ProjID
    AND 
       c.CreatedDate LIKE '2021-08%' 
  LEFT JOIN LastEntries AS f
WHERE
    a.Project_Delivered != 'Yes'
  AND
    a.PMO_Board_Report != 'No'
  AND
    a.Status == 'Acknowledged'
  AND
    a.InsertDate = f.pl_insert
  AND
    b.InsertDate = f.pe_insert
  AND
    a.ProjID = f.projid
GROUP BY a.ProjID
ORDER BY a.Manager, a.ProjID
;

(45) By jose isaias cabrera (jicman) on 2021-08-12 15:39:59 in reply to 44 [link] [source]

Thank you very much.

josé

(41) By jose isaias cabrera (jicman) on 2021-08-11 15:29:39 in reply to 37 [link] [source]

	LEFT JOIN LastEntries AS f
		ON a.ProjID = f.ProjID
		AND a.InsertDate = f.pl_insert AND b.InsertDate = f.pe_insert

These changes produce a totally different set of records. Hmmmm...Thanks.

(43) By Rico Mariani (rmariani) on 2021-08-11 22:51:10 in reply to 41 [link] [source]

If you want to filter 'a' using 'f' then by all means keep it in the WHERE.