Double count problem. Who can help?

[TGOS]

Here's a little question to MySQL Gurus.

Two tables. One called 'document' one called 'page'. Needles to say, a
document consist out of multiple pages (1:n). The primary (P) and
foreign (F) keys are:

*** document
document_id (P)
*** page
page_id (P)
document_id (F)
Now the task:

How many documents have more than ten pages?
And no subselects please.
I can do the following to filter out all documents with ten or less
pages:

SELECT document.docume nt_id, COUNT(page_id) as pages FROM document INNER
JOIN page ON page.document_i d=document.docu ment_id WHERE pages>10 GROUP
BY page_id
But how can I now run a count over the whole thing?

--
TGOS

[Siemel Naran]

"TGOS" <tg**@invalid.i nvalid> wrote in message

SELECT document.docume nt_id, COUNT(page_id) as pages FROM document INNER
JOIN page ON page.document_i d=document.docu ment_id WHERE pages>10 GROUP
BY page_id

But how can I now run a count over the whole thing?

In MySql 4.1 it is just

select count(*) from
(select ...) as whatever_name
In most versions of MySql you can also do

select @var:=0;

select document.id, count(page.id) as pages, @var:=@var+1
from
document
inner join page on document.id = page.document_i d
group by document.id
having pages > 1

and the 3rd column @var:= holds the count.

In any version of MySql, you can also always create a new table to store the
results of the sub-query, and populate it with an insert ... select
statement. Internally, this is how MySql would handle some sub-queries. To
create a temporary table visible to only one connection, do the following:

create temporary table_name ...

[Siemel Naran]

TGOS <tg**@invalid.i nvalid> wrote in message

*** page
page_id (P)
document_id (F)
Is there a column for page number? If so, then you can just join
document to page where the page number is 11, and this means the
document has more than 10 pages.
Now the task:

How many documents have more than ten pages?
And no subselects please.

select count(*) from
document D
inner join page P1 on D.id = P1.document_id
inner join page P2 on D.id = P2.document_id and (P1.id <> P2.id)
inner join page P3 on D.id = P3.document_id and (P1.id <> P2.id and
P1.id <> P3.id)
....
Maybe the older versions of MySql support IN statements for fixed
constants (rather than a select statement) so you could try

select count(*) from
document D
inner join page P1 on D.id = P1.document_id
inner join page P2 on D.id = P2.document_id and P2.id not in (P1.id)
inner join page P3 on D.id = P3.document_id and P3.id not in (P1.id,
P2.id)
....

To get the documents having fewer than 5 pages you can left outer join
and a query on P5.id is NULL.