Hello all,
I have a table and two queries designed on it. Queries return different results. I am looking for a logic for it:
First Query
SELECT tableA.ID, Count(tableA.ID) As New, A.Field1, A.Field2, A.Field3
From TableA
Group by .......
Having Count(tableA.ID)>1
=============================================
Second Query
Select tableA.ID, tableA.Field1, tableA.Field2, TableA,Field3
From TableA
WHERE (TableA.ID) IN (SELECT ID From TableA As Temp Group by Field1, Field2, Field3 WHERE ID having Count(*)>1 AND FieldA=TableA.FieldA AND FieldB=Table.FieldB
===========================================
In reality the first Query gives 102 records and second one is more than 200.What is the difference and what does the second query gives that is missing in second one?
Thanks for shedding light on me.
BittenApple
group query Vs. SubQuery
-
- Administrator
- Posts: 78513
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: group query Vs. SubQuery
What is A in the first query? Did you mean tableA?
The second query mentions FieldA and FieldB. They aren't mentioned in the first query. So the queries cannot be compared directly.
Apart from that, the first queries is a Totals query that groups some field values together. The second query is not a Totals query (although it has a Totals query as subquery), so it does not group field values together.
The second query mentions FieldA and FieldB. They aren't mentioned in the first query. So the queries cannot be compared directly.
Apart from that, the first queries is a Totals query that groups some field values together. The second query is not a Totals query (although it has a Totals query as subquery), so it does not group field values together.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: group query Vs. SubQuery
Hello Hans,
What is A in the first query? Did you mean tableA?
Sorry, I mean table A
============================
My error:
The first query should be:
SELECT tableA.ID, Count(tableA.ID) As New, A.FieldA, A.FieldB
From TableA
Group by .......
Having Count(tableA.ID)>1
====================
They are two queries written on one table.
One as you mentioned is a total query and the second query is a subquery. How are they different in bringing the result back?
Please let me know if this is not clear.
So many thanks,
BittenApple
What is A in the first query? Did you mean tableA?
Sorry, I mean table A
============================
My error:
The first query should be:
SELECT tableA.ID, Count(tableA.ID) As New, A.FieldA, A.FieldB
From TableA
Group by .......
Having Count(tableA.ID)>1
====================
They are two queries written on one table.
One as you mentioned is a total query and the second query is a subquery. How are they different in bringing the result back?
Please let me know if this is not clear.
So many thanks,
BittenApple
-
- Administrator
- Posts: 78513
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: group query Vs. SubQuery
In a Totals query records with the same values in all Group By fields are displayed only once.
Otherwise, those records will be displayed individually.
Play around with some simple Totals queries to see the effect.
Otherwise, those records will be displayed individually.
Play around with some simple Totals queries to see the effect.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: group query Vs. SubQuery
Hello Hans,
I can understand total query better than sub query. I am able to visualize grouping and doing some calculations on them better subquery, I need some light on it, I think the inner query checks and evaluates to true and false, is this right? And what happens next? Regards bittenapple
I can understand total query better than sub query. I am able to visualize grouping and doing some calculations on them better subquery, I need some light on it, I think the inner query checks and evaluates to true and false, is this right? And what happens next? Regards bittenapple
-
- Administrator
- Posts: 78513
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: group query Vs. SubQuery
The subquery returns all ID values for which there is more than one record with the same FieldA and FieldB.
The main query selects all records with those ID values.
The main query selects all records with those ID values.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: group query Vs. SubQuery
Hello Hans, a very good explanation!!! Regards, bittenapple