TOP 100 query - problems

[kristinac]

Good morning...

I am trying to run a query that requests the Top 100 (or 200 or whatever based on the user's choice) records from my database. Well to be more specific, I have a streets table and an addresses table. I want the top 100 streets, and all the addresses that correspond to those streets. This is what the statement looks like right now:

SELECT TOP 100 Streets.City, Streets.Name, Streets.Name + ' ' + Streets.Type as Street, Addresses.Address, Addresses.Resident, [addresses.etc...], 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 Streets.City = "WHITEVILLE" AND Addresses.Address BETWEEN Segments.BAddress AND Segments.EAddress ORDER BY Streets.Name, Addresses.Address

All the joins and everything work as far as I get the proper information for the records that are returned. However, I am getting the wrong records. I am only getting 100 addresses and a couple streets. I want to say

SELECT TOP 100 Streets and all addresses where addresses.streetid = streets.id.

But I can't seem to figure out how to get 100 streets and all related addresses in one sql statement.

Any ideas would be appreciated...Thanks in advance...

 

[TonyJollans]

Hi kristinac,

You need to use a Sub-Select for this one ..

SELECT Streets.City, Streets.Name, Streets.Name + ' ' + Streets.Type as Street, Addresses.Address, Addresses.Resident, [addresses.etc...], 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 100 Streets.ID FROM Streets WHERE Streets.City = "WHITEVILLE&quot
AND Addresses.Address BETWEEN Segments.BAddress AND Segments.EAddress ORDER BY Streets.Name, Addresses.Address

.. I haven't studied your SQL so I may not have all the bits completely correct but the sub-select is the key.

Enjoy,
Tony

 

[kristinac]

Thanks very much Tony...That's working exactly the way I wanted. However, now I've got random streets either being listed twice or left out altogether. I have no clue why...

I keep solving one problem and finding another! :~o

 

[TonyJollans]

Hi kristinac,

WIthout problems the world wouldn't need us

Glad you've got over one problem anyway. If you need more help, post again with the amended SQL - I or someone else will look at it.

Enjoy,
Tony