Explanation of HAVING Function

[MNNovice]

Recently I posted a problem to generate a report which will pull titles of DVDs where two casts are common. My table name was ItemsCast which linked two tables (Items and Cast) and the field is called iccID. NeoPa sent me the SQL for a query which works like magic. But I just needed to know an explanation of the HAVING function that was a part of the SQL. Can someone please explain the terms: Min / Max and how it is working? The entire SQL is given below: I would like to know an explanation of line 5, please. Thanks.

SELECT ItemsCast.iciID, Items.iNo, Items.iTitle, Items.iID, Items.SubTitle, Items.Year, Items.Color, Items.Note, Category.cgAbbv, Language.lName, Format.fAbbv, Type.tName
FROM ([Language] INNER JOIN (Type INNER JOIN (Format INNER JOIN (Category INNER JOIN Items ON Category.cgID = Items.cgID) ON Format.fID = Items.fID) ON Type.tID = Items.tID) ON Language.lID = Items.lID) INNER JOIN ItemsCast ON Items.iID = ItemsCast.iciID
WHERE (((ItemsCast.iccID) In ([Forms].[fReportList].[txtDirector],[Forms].[fReportList].[txtActor])))
GROUP BY ItemsCast.iciID, Items.iNo, Items.iTitle, Items.iID, Items.SubTitle, Items.Year, Items.Color, Items.Note, Category.cgAbbv, Language.lName, Format.fAbbv, Type.tName
HAVING (((Min(ItemsCast.iccID))<Max([iccID])));

[MMcCarthy]

I'm afraid I will have to leave the explanation on this one to NeoPa. He's obviously come up with a very inventive way of using the HAVING clause to show all results for when the actor or director cast id appears in another item. I'm afraid I can't follow exactly what he did though so I will ask him to take a look at this and hopefully we'll both learn something :)

[MNNovice]

I'm afraid I will have to leave the explanation on this one to NeoPa. He's obviously come up with a very inventive way of using the HAVING clause to show all results for when the actor or director cast id appears in another item. I'm afraid I can't follow exactly what he did though so I will ask him to take a look at this and hopefully we'll both learn something :)

Thanks for looking into it. Looking at the codes I can tell it's an innovative way of getting the result. That NeoPa is a genius!!!

[NeoPa]

This will be much easier to explain and understand if we start with the original SQL I suggested (See Query parameters through a Form). There is little extraneous info to confuse the fundamental issue. I suspect Mary may not need the explanation now, as most of the confusion was the complicated extras in the SQL posted, and the fact that the lines are too long to show in their entirety.

SELECT   [iciID]

FROM     [ItemCast]

WHERE    (iccID In(Forms.fReportList.cboDirector
                  ,Forms.fReportList.cboActor))

GROUP BY [iciID]

HAVING   (Min(iccID)<Max(iccID))

The above SQL selects only those records from the table [ItemCast] (A Many-to-Many linking table) where [iccID] (the FK for the [Cast] table) matches the value of either of the ComboBoxes on the form.

The HAVING clause (Bear in mind this is qualified by the GROUP BY clause.) is activated on the resultant grouped data, and selects only those results where there is more than one value for [iccID]. As there are only two possible values anyway (as specified by the WHERE clause) this must be selecting only those result sets where both values are found.

[NeoPa]

By the way - Don't get too hung up on the table name I used ([ItemCast]). It's different from the latest SQL MNN has posted, but it reflects the name as given in the linked thread. Obviously whatever the correct table name is, should be used.

[MMcCarthy]

OK so the iciid values returned represent the director and the actor for each item. I still don't understand why we use the min < max clause. I am probably misunderstandin g the results MNN is trying to achieve from the query. Don't get too bogged down in explaining if your explanation satisfies MNN we can talk later :D

[NeoPa]

If [iciID] refers to a film, and [iccID] refers to a cast member (whether that be a an actor or a director (or indeed anyone else who contributes to the film) then before the HAVING clause is added you have all films where either the selected actor or the selected director is involved (or both of course).

The HAVING clause excludes those sets of data where either the actor alone, or the director alone, is included.

[MNNovice]

NeoPa:

Thanks for the explanation. I am afraid it's not yet clear to me HOW the HAVING clause is excluding :

those sets of data where either the actor alone, or the director alone, is included.

I mean where in this code

HAVING   (Min(iccID)<Max(iccID))

that exclusion is happening. Just wanted to know/learn. If this is too complicated and takes too much of your time then don't worry.

Many thanks. M

[NeoPa]

We start from a position where only two values are selected (WHERE clause) from [iccID]. If we talk about these as A & B for now that may make it easier to follow.

There are basically 3 logical possibilities, as at least one of these must be True for the data to have got past the WHERE clause. These are A only is found; B only is found; Both A & B are found.

If A only is found then the minimum value will reflect A, and the maximum value will also reflect A. Min([iccID])<Max([iccID]) will not be True as both will be A.

The same is true for B only (except in reverse).

However, when both A and B are found, then Min([iccID]) will be A and Max([iccID]) will be B. A is less than B so the result is True.

Thus only those Items where both A and B are included will be selected.

[MMcCarthy]

We start from a position where only two values are selected (WHERE clause) from [iccID]. If we talk about these as A & B for now that may make it easier to follow.

There are basically 3 logical possibilities, as at least one of these must be True for the data to have got past the WHERE clause. These are A only is found; B only is found; Both A & B are found.

If A only is found then the minimum value will reflect A, and the maximum value will also reflect A. Min([iccID])<Max([iccID]) will not be True as both will be A.

The same is true for B only (except in reverse).

However, when both A and B are found, then Min([iccID]) will be A and Max([iccID]) will be B. A is less than B so the result is True.

Thus only those Items where both A and B are included will be selected.

Eureka, I get it now. How does your brain come up with these things. :)

[NeoPa]

That's very kind Mary.

I have to keep finding solutions as people keep asking the questions.

[MNNovice]

That's very kind Mary.

I have to keep finding solutions as people keep asking the questions.

NeoPa:

Thanks a lot. All I can say, "Wow!!!" Until next time.

[MNNovice]

That's very kind Mary.

I have to keep finding solutions as people keep asking the questions.

NeoPa,

I still have one question. Could it not be done with an "AND" in the WHERE clause? Why HAVING clause was needed?

For example, could we not write it this way?

WHERE (iccID In(Forms.fRepor tList.cboDirect or
AND Forms.fReportLi st.cboActor))

Can you please explain this a little for novices like myself?

Thanks.

[NeoPa]

Certainly.

The WHERE clause applies to each incoming record individually. If a record were required to contain both A and B in the same field, a field that can only contain a single item, then no data would ever be selected.

Currently the WHERE clause has the equivalent of A OR B anyway. This is necessary of course as, without this, the concept wouldn't work.

Consider that the WHERE clause works at an incoming record level. This processes before the grouping is applied. It is not logically possible to identify which grouping contains both A and B, before the grouping is even applied.

The HAVING clause, on the other hand, works with the already grouped records, and thereby has the information available with which to determine if a grouping contains both A and B.

[B]Film     CastMember[/B]
2001         G
2001         Z
2002         A
2002         A
2002         D
2002         L
2002         Z
2003         B
2003         G
2003         L
2010         A
2010         B
2010         B
2010         G
2010         L
2010         T
2010         Z

Consider the data above. You will see that only the film 2010 has both A and B as cast members of any form. 2001 has neither, 2002 has A (twice in separate roles for good measure), 2003 has B but only 2010 has both (with B in there twice).

Let's see what SQL is left with after it has applied its WHERE clause :

[B]Film     CastMember[/B]
2002         A
2002         A
2003         B
2010         A
2010         B
2010         B

Much less to worry about clearly. 2001 is already gone from the equation. We know already that it is not possible for that to match our criteria.

Now let's see what we are left with after the grouping :

[B]Film     CastMember[/B]
2002         (A, A)
2003         (B)
2010         (A, B, B)

In this case we show all the remaining values for CastMember in a list within parentheses ().

At this point (and only this point as it cannot make any sense beforehand) we apply the HAVING clause. You can easily see that only one grouped record matches our HAVING criteria. 2002 and 2003 both have valid data that has passed through the WHERE clause, but both have only one value, even though 2002 has two items of the same value, in the list. Only 2010 passes the criteria you requested, which is that both values (cast members) must be present for a film to be included.

[MNNovice]

Certainly.

The WHERE clause applies to each incoming record individually. If a record were required to contain both A and B in the same field, a field that can only contain a single item, then no data would ever be selected.

Currently the WHERE clause has the equivalent of A OR B anyway. This is necessary of course as, without this, the concept wouldn't work.

Consider that the WHERE clause works at an incoming record level. This processes before the grouping is applied. It is not logically possible to identify which grouping contains both A and B, before the grouping is even applied.

The HAVING clause, on the other hand, works with the already grouped records, and thereby has the information available with which to determine if a grouping contains both A and B.

[B]Film     CastMember[/B]
2001         G
2001         Z
2002         A
2002         A
2002         D
2002         L
2002         Z
2003         B
2003         G
2003         L
2010         A
2010         B
2010         B
2010         G
2010         L
2010         T
2010         Z

Consider the data above. You will see that only the film 2010 has both A and B as cast members of any form. 2001 has neither, 2002 has A (twice in separate roles for good measure), 2003 has B but only 2010 has both (with B in there twice).

Let's see what SQL is left with after it has applied its WHERE clause :

[B]Film     CastMember[/B]
2002         A
2002         A
2003         B
2010         A
2010         B
2010         B

Much less to worry about clearly. 2001 is already gone from the equation. We know already that it is not possible for that to match our criteria.

Now let's see what we are left with after the grouping :

[B]Film     CastMember[/B]
2002         (A, A)
2003         (B)
2010         (A, B, B)

In this case we show all the remaining values for CastMember in a list within parentheses ().

At this point (and only this point as it cannot make any sense beforehand) we apply the HAVING clause. You can easily see that only one grouped record matches our HAVING criteria. 2002 and 2003 both have valid data that has passed through the WHERE clause, but both have only one value, even though 2002 has two items of the same value, in the list. Only 2010 passes the criteria you requested, which is that both values (cast members) must be present for a film to be included.

NeoPa:

Thanks for the detailed step by step explanation. Excellent! You should consider teaching...

So this is what I understood. Let me explain. Suppose the A = [iccID] 13 and B = [iccID] 75. Now after the grouping of [iciID], minimum of [iccID] 13 cannot also be maximum of [iccID] 13. Or minimum of [iccID] 75 cannot be maximum of [iccID] 75.

So the HAVING clause is evaluating the criteria this way: Find minimum of [iccID] 13 where maximum of [iccID] 75 exists.


Am I on right track?

Thank you very much. Have a good weekend.