complicated query - help please!

[kristinac]

I have a sql statement that involves aliases, the TOP keyword, and inner joins. The statement that produces the first page of my report works like a charm. However, the statement that gets the info for page 2, 3, etc, is doing something odd. I'm supposed to get 50 records (streets) at a time. Somehow I'm getting 26-48, then it skips 49 & 50 and gets 51-75 (like it's supposed to), then I get 76-99, and it skips 100 & 101, then gives me 102-125. So every other page it gives me exactly 25, the other pages it skips records and returns less than 25.

Here is my statement. Forgive me, it's rather long...

SELECT Streets.City, Streets.Name, Streets.Name + ' ' + Streets.Type as Street, ...ETC Many Fields..., Addresses.PIN, ESNs.ESN FROM Streets INNER JOIN (Addresses INNER JOIN (Segments INNER JOIN ESNs ON Segments.ESNId = ESNs.Id) ON Addresses.StreetId = Segments.StreetId) ON Streets.Id = Segments.StreetId WHERE Addresses.StreetId IN (SELECT TOP " & PageSize & " Streets.Id FROM Streets WHERE Streets.City = ? AND Streets.Id NOT IN (SELECT TOP " & (PageSize * (Page - 1)) & " Streets.Id FROM Streets WHERE Streets.City = ? ORDER BY Streets.Name) ORDER BY Streets.Name) AND Addresses.Address BETWEEN Segments.BAddress AND Segments.EAddress ORDER BY Streets.Name, Streets.Type, Addresses.Address

The PageSize is 25, and ? is the town chosen by the user. The statement that gets the first 25 is very similar and works great. Why am I not getting exactly 25 records every time? I would appreciate any assistance...Thanks!

 

[substitute]

Judging from your profile you are probably better at this than me, so this might not be a lot of use. However I recreated the essence of your SQL on one of my own databases and it worked fine.

Because of that I suggest you deconstruct the SQL until it works again, then rebuild it once you've found the problem. Start with removing the extra ORDERs at the bottom as I didn't put those in my test, and continue by removing the deepest inner join as I didn't put that in either (I have to work too!)

I wouldn't set any store by the fact page 1 works, so does page 3!

It would also be worth hard coding the page numbers and running the SQL as a query - that is how I tested it. That would eliminate any problems with the VBA variables and concatenation.

Finally, I love the way you wrote the SQL but is it needed?
What is wrong with just throwing a new page every 25 records? (put a page break control in the detail section, count records in the detail section and make the break visible every time the count gets to 25)

Good luck

 

[kristinac]

Well as it turns out, I wasn't getting the records because some were not connected to all tables. I had addresses in the address table that were not between any segment ranges so they were not being retrieved. The sql was fine, it's the data that is peculiar.

I need this statement (as opposed to using page breaks) because it's actually retrieving every record associated with the 25 chosen streets. The actual number of records per page varies. And the users will be adding more records so the number selected will be changing too.

Thanks for your help! I discovered that the sql was correct because I tore it apart as you suggested and finally realized my mistake.

 

[Golom]

Can't be sure without seeing the data but my guess is that this construct (abbreviated for readability) is the problem.

WHERE Addresses.StreetId IN
(SELECT TOP " & PageSize & " Streets.Id FROM ... AND Streets.Id NOT IN (SELECT TOP " & (PageSize * (Page - 1)) & ...)

It works OK on Page 1 because the second SubSelect returns zero records (TOP 0). The first query always returns the same 25 (i.e. pagesize) streets. The second subquery then eliminates 0 on page 1, up to 25 on page 2, up to 50 on page 3, etc. My assumption is that on pages after page 1, some of the streets being returned by the first subselect are being dropped by the second one so that you get fewer than 25 streets. I suspect that, if you change the first SubSelect to

Select TOP & PageSize * Page & ...

You will get all the streets up to the current page and then the second SubSelect will eliminate those on previous pages.

 

[kristinac]

Okay I tried that, but the way the statement is set up it gets (PageSize * Page) records. I want the second 25 records, for example. When I use PageSize * Page I get the first 50 records after the first 25. Which is actually why I wrote the statement that way...before I had an even longer sql statement that would grab records then flip them then grab the right number then flip them again. It was even more confusing.

But yes thank you, that was a good idea. It does skip the records it should skip, but it also returns too many...

 

[kristinac]

Okay - I changed the order of my tables in my FROM clause AND changed every INNER JOIN to a LEFT JOIN. Now I get exactly 20 recs per page, every street is represented...So that's what I was missing...

Thanks for the help figuring this out. It really helps to come here and hash things out with other people!