Help needed for a twisted SQL query

Question

Post reply

Help needed for a twisted SQL query

Viewing 4 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Did you try this:

DECLARE @Boston_prdID (numeric)

SET @Boston_prdID = 555

SELECT p.*, c.*, d.DistanceToBoston AS [MinDistanceToBoston]

FROM PRODUCTS p

INNER JOIN OTM o ON o.prdID = p.prdID

INNER JOIN CITIES c ON c.postcodeID = o.postcodeID

INNER JOIN (SELECT pd.prdID, MIN(dbo.getDistanceFromID(@Boston_prdID, cd.latitude, cd.longitude)) AS [DistanceToBoston]

FROM PRODUCTS pd

INNER JOIN OTM od ON od.prdID = pd.prdID

INNER JOIN CITIES cd ON cd.postcodeID = od.postcodeID

GROUP BY pd.prdID

) d ON d.prdID = p.prdID AND d.[DistanceToBoston] = dbo.getDistanceFromID(@Boston_prdID, c.latitude, c.longitude)

ORDER BY p.prdID, d.DistanceToBoston

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

chris-959272 SSC Veteran Points: 276 More actions · Answer 2

Chris Morris (12/5/2008)
The definition fior the cities table doesn't include lambertX and lambertY

:blush:

my mistake. Just replace latitude by lambertX and longitude by lambertY in the CITIES definitions.

Sorry!

Chris

chris-959272 SSC Veteran Points: 276 More actions · Answer 3

Chris Morris (12/5/2008)
Did you try this:
DECLARE @Boston_prdID (numeric)
SET @Boston_prdID = 555
SELECT p.*, c.*, d.DistanceToBoston AS [MinDistanceToBoston]
FROM PRODUCTS p
INNER JOIN OTM o ON o.prdID = p.prdID
INNER JOIN CITIES c ON c.postcodeID = o.postcodeID
INNER JOIN (SELECT pd.prdID, MIN(dbo.getDistanceFromID(@Boston_prdID, cd.latitude, cd.longitude)) AS [DistanceToBoston]
FROM PRODUCTS pd
INNER JOIN OTM od ON od.prdID = pd.prdID
INNER JOIN CITIES cd ON cd.postcodeID = od.postcodeID
GROUP BY pd.prdID
) d ON d.prdID = p.prdID AND d.[DistanceToBoston] = dbo.getDistanceFromID(@Boston_prdID, c.latitude, c.longitude)
ORDER BY p.prdID, d.DistanceToBoston

Chris,

You are now officially my hero!!! :w00t:

This works perfectly.

I really want to thank you for helping me out of this, it's very much appreciated.

Chris

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

We got there in the end! Thanks for the feedback Chris.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden