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])));
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.
15 2679
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 :)
@msquared
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 32,556
Expert Mod 16PB
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 32,556
Expert Mod 16PB
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.
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 misunderstanding 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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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.
@NeoPa
Eureka, I get it now. How does your brain come up with these things. :)
NeoPa 32,556
Expert Mod 16PB
That's very kind Mary.
I have to keep finding solutions as people keep asking the questions.
@NeoPa
NeoPa:
Thanks a lot. All I can say, "Wow!!!" Until next time.
@NeoPa
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.fReportList.cboDirector AND Forms.fReportList.cboActor))
Can you please explain this a little for novices like myself?
Thanks.
NeoPa 32,556
Expert Mod 16PB
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. - Film CastMember
-
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 : - Film CastMember
-
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 : - Film CastMember
-
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
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.
NeoPa 32,556
Expert Mod 16PB
That's not quite how I'd express it, but it certainly sounds like you have a grasp of what it's doing, yes.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: CoolPint |
last post by:
While I was testing my understanding of Functioin Template features by
playing with simple function templates, I got into a problem which I
cannot understand. I would be very grateful if someone...
|
by: James Fortune |
last post by:
I'm doing some computations in order to do capacity planning. Instead
of using some function from an Access book to do the weekday
calculation, I decided to come up with an alternate method since...
|
by: jimfortune |
last post by:
From:
http://groups-beta.google.com/group/comp.databases.ms-access/msg/769e67e3d0f97a90?hl=en&
Errata:
19 solar years = 2939.6018 days should be
19 solar years = 6939.6018 days
Easter...
|
by: Nitin |
last post by:
Ppl ,
Want to have ur opinions on having function calls like the one stated
below:
function funcA ( struct A *st_A , struct B *st_B )
{
st_A->a = st_B->a
|
by: Morgan Cheng |
last post by:
Hi All,
I was taught that argument valuse is not supposed to be changed in
function body. Say, below code is not good.
void foo1(int x)
{
x ++;
printf("x+1 = %d\n", x);
}
It should be...
|
by: leaf |
last post by:
Hi,
How to call function at runtime,
based on a struct that contains the information for the function call:
struct func_to_call {
int function_id; // function id to call
unsigned int nparams;...
|
by: Dmitry Anikin |
last post by:
I mean, it's very convenient when default parameters
can be in any position, like
def a_func(x = 2, y = 1, z):
...
(that defaults must go last is really a C++ quirk which
is needed for overload...
|
by: sam_cit |
last post by:
Hi Everyone,
int main()
{
printf("not included stdio.h");
}
Yes, i haven't included stdio.h and my compiler would generate a
warning and would assume that it would return a int, my question...
|
by: Anonymous |
last post by:
Came across some code summarized as follows:
char const* MyClass::errToText(int err) const
{
switch (err)
{
case 0: return "No error";
case 1: return "Not enough";
case 2: return "Too...
|
by: venkat |
last post by:
Hi,
I have written a program
void main()
{
printf("%d %d\n", sizeof main, sizeof(main()));
}
in this program the output is 1 and 4,
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |