retrive only 20 for each execution

S

sapta

dear all
I've 2 tables A WITH 1000 RECORDS
B WITH 4 RECORDS

SELECT * FROM A,B will give me 4000 records of the result,

how can we get 20 records for every multiply A to B
so the final result we have 80 records.(20*4) in single
SQL statements ?

thanks in advance

sapta
 
J

John Mishefske

sapta said:
dear all
I've 2 tables A WITH 1000 RECORDS
B WITH 4 RECORDS

SELECT * FROM A,B will give me 4000 records of the result,

how can we get 20 records for every multiply A to B
so the final result we have 80 records.(20*4) in single
SQL statements ?
Click to expand...

this would be a cartesian join, that is for every record in A
you would have 4 records in B giving (as you stated) 1000*4 or
4000 records.

To get 20 at a time requires some VBA coding. You can get the
first 20 with:

SELECT TOP 20 * FROM A, B ORDER BY uniqueIdentifier

but beyond that requires some unique identifier such that you
could write:

SELECT TOP 20 * FROM A, B
WHERE uniqueIdentifier NOT IN
(SELECT TOP 20 * FROM A, B ORDER BY uniqueIdentifier)
ORDER BY uniqueIdentifier

or something along these lines...
 
Top