Fejl i query

Har du prøvet med en SELECT DISTINCT ??

ja, det løste desværre ikke problemet.....

Kan ikke svar uden at har din SQL statement...men det ser ud som du ikke brug JOIN, bare SELECT .... FROM TransFleet.dbo.OrderParcelNumbers, TransFleet.dbo.Orders, TransFleet.dbo.CargoLine WHERE .....

Brug INNER JOIN

Min query ser ud som følger:

SELECT distinct OrderParcelNumbers.ParcelNumber, Orders.OrderId, Orders.ExternOrderId, CargoLine.Weight, CargoLine.GoodsDescription, Orders.ReceivedTimeStatus1 FROM TransFleet.dbo.CargoLine CargoLine, TransFleet.dbo.OrderParcelNumbers OrderParcelNumbers, TransFleet.dbo.Orders Orders WHERE CargoLine.OrderId = Orders.OrderId AND OrderParcelNumbers.OrderId = Orders.OrderId AND ((CargoLine.GoodsDescription='Posten Ekspr') AND (Orders.ReceivedTimeStatus1>{ts '2003-06-12 00:00:00'} And Orders.ReceivedTimeStatus1<{ts '2003-06-12 23:59:59'}) OR (CargoLine.GoodsDescription='Post') AND (Orders.ReceivedTimeStatus1>{ts '2003-06-12 00:00:00'} And Orders.ReceivedTimeStatus1<{ts '2003-06-12 23:59:59'}))

Du må gerne komme med et eksempel på en INNER JOIN.....

hov der kom lidt flere variabler med i selecten, men det betyder ikke noget...

Undskyld, jeg har lige læset mailen nu.

Prøve:

SELECT distinct OrderParcelNumbers.ParcelNumber, Orders.OrderId, Orders.ExternOrderId, CargoLine.Weight, CargoLine.GoodsDescription, Orders.ReceivedTimeStatus1
FROM (Orders INNER JOIN OrderParcelNumbers ON Orders.OrderId = OrderParcelNumbers.OrderId) INNER JOIN CargoLine ON Orders.OrderId = CargoLine.OrderId
WHERE ((CargoLine.GoodsDescription='Posten Ekspr') AND (Orders.ReceivedTimeStatus1>{ts '2003-06-12 00:00:00'} And Orders.ReceivedTimeStatus1<{ts '2003-06-12 23:59:59'}) OR (CargoLine.GoodsDescription='Post') AND (Orders.ReceivedTimeStatus1>{ts '2003-06-12 00:00:00'} And Orders.ReceivedTimeStatus1<{ts '2003-06-12 23:59:59'}))

Hej du, jeg har prøvet din query, men der er desværre stadig dubletter. Her er mit resultat:

kollinummer  orderid Ex.O.id vægt                Goodsd dato
106777113326 2075062 1067771 0.22                Post  2003-06-12 16:27:34.000
107055113322 2075140 1070551 1.3999999999999999  Post  2003-06-12 16:17:01.000
107157613330 2074886 1071576 3.52                Post  2003-06-12 16:34:35.000
107281813327 2075079 1072818 0.81000000000000005 Post  2003-06-12 16:29:04.000
107385413331 2074898 1073854 3.52                Post  2003-06-12 16:36:37.000
107385413332 2074898 1073854 3.52                Post  2003-06-12 16:36:37.000
107528413328 2074911 1075284 3.6000000000000001  Post  2003-06-12 16:31:04.000
107534613329 2074953 1075346 1.8                Post  2003-06-12 16:33:05.000
107548613324 2075132 1075486 0.73999999999999999 Post  2003-06-12 16:25:05.000
107548613324 2075132 1075486 5.0                Post  2003-06-12 16:25:05.000
107548613325 2075132 1075486 0.73999999999999999 Post  2003-06-12 16:25:05.000
107548613325 2075132 1075486 5.0                Post  2003-06-12 16:25:05.000
107554013323 2075103 1075540 9.5                Post  2003-06-12 16:19:02.000
107555313321 2075130 1075553 8.0                Post  2003-06-12 16:13:02.000
107561713340 2075230 1075617 1.1599999999999999  Post  2003-06-12 19:37:42.000
107570613336 2075265 1075706 1.0                Post  2003-06-12 18:15:59.000
107570613336 2075265 1075706 16.0                Post  2003-06-12 18:15:59.000
107570613337 2075265 1075706 1.0                Post  2003-06-12 18:15:59.000
107570613337 2075265 1075706 16.0                Post  2003-06-12 18:15:59.000
107570613338 2075265 1075706 1.0                Post  2003-06-12 18:15:59.000
107570613338 2075265 1075706 16.0                Post  2003-06-12 18:15:59.000

Der er et par dubletter...

OK, måske vi skal grupper også.  Jeg forset på Danglish.

The query we built assumes that there is a one to one relation between the tables.  That is that we have one post in each of the 3 tables.

TransFleet.dbo.OrderParcelNumbers 
TransFleet.dbo.Orders
TransFleet.dbo.CargoLine

You can see that there are NO duplicates, every post is different from the next based on the fields kollinummer, orderid, Ex.O.id, vægt.

If you want to see sum of vægt (instead of an individual post for each) you will need to group the SQL. Something like:

SELECT DISTINCT OrderParcelNumbers.ParcelNumber, Orders.OrderId, Orders.ExternOrderId, Sum(CargoLine.Weight) AS SumOfWeight, CargoLine.GoodsDescription, Orders.ReceivedTimeStatus1
FROM (Orders INNER JOIN OrderParcelNumbers ON Orders.OrderId = OrderParcelNumbers.OrderId) INNER JOIN CargoLine ON Orders.OrderId = CargoLine.OrderId
WHERE ((CargoLine.GoodsDescription='Posten Ekspr') AND (Orders.ReceivedTimeStatus1>{ts '2003-06-12 00:00:00'} And Orders.ReceivedTimeStatus1<{ts '2003-06-12 23:59:59'}) OR (CargoLine.GoodsDescription='Post') AND (Orders.ReceivedTimeStatus1>{ts '2003-06-12 00:00:00'} And Orders.ReceivedTimeStatus1<{ts '2003-06-12 23:59:59'}))
GROUP BY OrderParcelNumbers.ParcelNumber, Orders.OrderId, Orders.ExternOrderId, CargoLine.GoodsDescription, Orders.ReceivedTimeStatus1

Det også betyder at du vil har duplicater hvis andre felt i CargoLine (GoodsDescription) er forskelig mellem den Sum() poster.

hmm, okay
Når du laver "Sum of Weight", kan jeg så være sikker på at jeg har den rigtige vægt for den rigtige kollinummer ?

Well, it's a sum.

Lets look at the two posts:
107570613337 2075265 1075706 1.0                Post  2003-06-12 18:15:59.000
107570613337 2075265 1075706 16.0                Post  2003-06-12 18:15:59.000

They become 1 post (with the group query):
107570613337 2075265 1075706 17.0                Post  2003-06-12 18:15:59.000

Because we SUM(CargoLine.Weight).

yes, that much i have understood :-)

but what weight is the correct one ? Your eksample shows the same kollinr with two different weights and only one of them is right. The other weight "belongs" to the other kollinr on that OrderId.
I just think that there should be a way, that ensures I get the right information to what ever kollinr/OrderId I have.