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

Have a look at the ClientID dupes in the bargains table. Are they really different clients, or do they differ in spelling, or missing country code?

Is this a one-off process,...

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 =...

SELECT @lX = lambertX, @lY = lambertY FROM CITIES WHERE postcodeID = @localID

The definition fior the cities table doesn't include lambertX and lambertY

SELECT *

FROM dbo.Bargains

WHERE CAST(ClientID AS NVARCHAR (10)) IN (

SELECT CAST(b.ClientID AS NVARCHAR (10)) AS ClientID

FROM dbo.Bargains b

WHERE NOT EXISTS (SELECT 1 FROM dbo.tblClients c WHERE c.ClientID COLLATE...

-- Set org_id to be same org_id as manager:

UPDATE p SET org_id = m.org_id

FROM Person p

INNER JOIN Person m ON m.emp_id = p.mgr_id

Thanks...this tells us that none of the rows in the bargains table have a match in the clients table according to the matching methods we've tried.

Time to retry the INSERT...

Nah, me stupid not you, sorry.

SELECT COUNT(*)

FROM dbo.Bargains b

--GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS...

I'm afraid your second query has a problem as well.

Now I get results where all postcodeID's are used for all products

That's the intention, sometimes a quick eyeball is all...

Hi Chris

There's an error in the JOIN, which is corrected here:

DECLARE @Boston_prdID (numeric)

SET @Boston_prdID = 555

SELECT p.*, c.*, dbo.getDistanceFromID(@Boston_prdID, c.latitude, c.longitude) AS [DistanceToBoston], d.DistanceToBoston AS [MinDistanceToBoston]

FROM PRODUCTS p

INNER JOIN...

A Little Help Please (12/5/2008)

---

Ok i did a stupid one... I forgot to run my SSIS package to populate the tables before I ran the statements.

I have now attched the...

A Little Help Please (12/5/2008)

---

Ok i did a stupid one... I forgot to run my SSIS package to populate the tables before I ran the statements.

I have now attched the...

Yep your absolutely right, this type of query is far more elegant in 2005.

Here's a query which will work.

[font="Courier New"]DECLARE @Boston_prdID (numeric)

SET @Boston_prdID = 555

SELECT p.*, c.*

FROM...

chris (12/5/2008)

---

Chris Morris (12/5/2008)

---

Ok lets confirm this:

Say your reference city is Boston, your requirement would be:

For each and every individual product (which has been sold), list the nearest city to...

Ok lets confirm this:

Say your reference city is Boston, your requirement would be:

For each and every individual product (which has been sold), list the nearest city to Boston where it's...