Make count = 0 where no row is returned.

  • Thread starter Thread starter Roger Hembury
  • Start date Start date
R

Roger Hembury

Hi

This has probably been asked quite a few times but I cannot find the
solution on Google so apologies for asking again.

I have two tables and I want to get a count of all the records in Table 2
but based on the records in Table 1.

Table 1
Field 1
A
B
C
D
E

Table 2
Field 1
A
B
C
D
A
B
C
D

The result I want to get is as follows -

A 2
B 2
C 2
D 2
E 0 <--- this is the bit I am having problems with :-(

All I can get at the moment is results A to D. E is completely ignored as
there are no records for it in Table 2.

I have seen this done before in one SQL statement but I cannot remember how
it was done.

Any pointers or the complete SQL statement would be appreciated.

Thanks

Roger
 
Roger Hembury said:
Hi

This has probably been asked quite a few times but I cannot find the
solution on Google so apologies for asking again.

I have two tables and I want to get a count of all the records in Table 2
but based on the records in Table 1.

Table 1
Field 1
A
B
C
D
E

Table 2
Field 1
A
B
C
D
A
B
C
D

The result I want to get is as follows -

A 2
B 2
C 2
D 2
E 0 <--- this is the bit I am having problems with :-(

All I can get at the moment is results A to D. E is completely ignored as
there are no records for it in Table 2.

I have seen this done before in one SQL statement but I cannot remember
how it was done.

Any pointers or the complete SQL statement would be appreciated.

Thanks

Roger
Click to expand...

No need to worry - finally figured it out. :-)

SELECT table1.Field1, (select count(table2.field1) from table2 where
table2.field1 = table1.field1) AS Expr1
FROM table1;
 
Roger Hembury said:
No need to worry - finally figured it out. :-)

SELECT table1.Field1, (select count(table2.field1) from table2 where
table2.field1 = table1.field1) AS Expr1
FROM table1;
Click to expand...

Or, probably more efficient,

SELECT table1.Field1, count(table2.field1) AS FieldCount
FROM table1 LEFT JOIN table2
ON table2.field1 = table1.field1
 
Douglas said:
Or, probably more efficient,

SELECT table1.Field1, count(table2.field1) AS FieldCount
FROM table1 LEFT JOIN table2
ON table2.field1 = table1.field1
Click to expand...

Indeed, I get an error most efficiently <g>: "You tried to execute a
query that does not include the specified expression 'Field1' as part
of an aggregate function".
 
When you add the missing

GROUP BY table1.Field1

clause, I still don't think this construct is more efficient than the
OP's subquery. I even suspect that at the SQL engine level, the two
approaches are synonymous.
 
Hi,


Correlated subqueries are generally less efficient than joins. A join is
a kind of specialization, in comparison with a more general correlated
subquery: for a general correlated subquery, you start from scratch for each
element of the outer "loop", while with join, consider you can "walk" in
parallel.

Example, take two telephone books and find clients name present in both
books.

With join, you run your fingers in parallel on EACH book, you have two
"pointers" always alive.

With subquery, you run your finger down over the smallest book, then,
open the other book, and search for a match, then "close" that second book
once it is done, and finally move down to the next name in the first book.

OK, you don't necessary have to close the book, but with a join, if you
look for a match for Azerty and if the second book indicates that the
"actual" name you point to with your finger (your index, probably) starts
with B, not only you know there is no Azerty in the second book, but you
know that in the first book, you can, there, move to the first name that
starts with B, skipping a lot of tries in this first book, since they will
all fail (that is a possible illustration of a cross-walk over the indexes).

Since subquery are more general, they can do much more than mere joins,
but because of that, I doubt optimizers would even consider to do with them
all the tricks they immediately KNOW they CAN logically do with joins. The
query plan can then be less efficient (or, at worst, equivalent) with
subqueries. There is just less safe optimization "tricks" doable with
subqueries. I don't say it is impossible, I just say that there is more work
to be done by the optimizer if you use subquery, if the optimizer knows how
to reach the equivalent plan of execution, in the first place.


Hoping it may help,
Vanderghast, Access MVP
 
Michel said:
Correlated subqueries are generally less efficient than joins.
Click to expand...

....So I decided to do some timed tests, using the SQL upthread, using
Table1 = 1K rows and Table2 = 100K rows, no indexes. Timed using
GetTickCount, there was nothing to choose between them (approx. average
9.75 seconds each).
 
Hi,


As in a <= b, then why choose a ? if the very best you can do, in
general, is to be as good as?


With no indexes, your strategy is to make a table scan, whatever your name
is Einstein or Joe Smo, database or spreadsheet, binary or ZML-ish ... well,
maybe not, in that latter case, since we can imagine some ZML that causes
your hard disk to spin for half a day.



Vanderghast, Access MVP
 
Back
Top