Cannot get full result from Joins

[Cpreston]

Hi

I have a query where I have added in 2 new tables so I can get the PackRef to display. However, is there is no packref it is not showing the result even though there is a row.
therefore I want to see all rows with or without a packref. The code is below. I have played with the joins for the ProductPAck and orderlineitem but either get 6 rows instead of 7 or to many rows completely.

Could someone advise please THanks

SELECT     TOP (100) PERCENT JourneyHeader.JourneyDate, CONVERT(varchar, JourneyHeader.JourneyNumber) AS JourneyNumber, JourneyLine.OrderID, 
                      JourneyHeader.NoOfDrops, JourneyzDropsByProduct.TotalVolume AS m3, dbo.Vehicle.udfVehicleGroup, 
                      dbo.Vehicle.Registration + ' - ' + dbo.Vehicle.Name AS VehicleName, JourneyzDropsByProduct.ProductCode, ProductGroup_1.Name AS [Group], 
                      ProductGroup.Name AS [Sub-group], Customer.CustomerCode, Customer.Name, Customer.City, dbo.ProductPack.PackRef
FROM         dbo.JourneyLine AS JourneyLine INNER JOIN
                      dbo.JourneyHeader AS JourneyHeader ON JourneyLine.JourneyID = JourneyHeader.JourneyID INNER JOIN
                      dbo.JourneyzDropsByProduct AS JourneyzDropsByProduct ON JourneyHeader.JourneyID = JourneyzDropsByProduct.JourneyID AND 
                      JourneyLine.JourneyLineID = JourneyzDropsByProduct.JourneyLineID INNER JOIN
                      dbo.Product AS Product ON JourneyzDropsByProduct.ProductID = Product.ProductID INNER JOIN
                      dbo.ProductGroup AS ProductGroup ON Product.ProductGroupID = ProductGroup.ProductGroupID INNER JOIN
                      dbo.OrderHeader AS OrderHeader ON JourneyLine.OrderID = OrderHeader.OrderID AND JourneyHeader.JourneyID = OrderHeader.JourneyID INNER JOIN
                      dbo.Customer AS Customer ON OrderHeader.CustomerID = Customer.CustomerID INNER JOIN
                      dbo.ProductGroup AS ProductGroup_1 ON ProductGroup.ParentID = ProductGroup_1.ProductGroupID INNER JOIN
                      dbo.Vehicle ON JourneyHeader.VehicleID = dbo.Vehicle.VehicleID INNER JOIN
                      dbo.ProductPack ON Product.ProductID = dbo.ProductPack.ProductID INNER JOIN
                      dbo.OrderLineItem ON OrderHeader.OrderID = dbo.OrderLineItem.OrderID AND dbo.ProductPack.PackID = dbo.OrderLineItem.PackID
WHERE      (JourneyHeader.JourneyNumber = 72732) and journeyline.OrderID IN (3338854,3338847,3338787)
ORDER BY JourneyNumber DESC

 

[gmmastros]

Change the inner joins on ProductPack and OrderLineItem to LEFT JOIN instead.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Cpreston]

Hi

Changed that but it brings in over a 1000 rows, should bring back about 54, Thanks

dbo.ProductPack ON Product.ProductID = dbo.ProductPack.ProductID LEFT JOIN
dbrderLineItem ON OrderHeader.OrderID = dbrderLineItem.Orde

 

[Olaf Doschke]

You should never guess which join type is needed, you have to know what you want. Saying you get too much and how many also doesn't help anybody to tell you what join type to choose and what where clauses to use for filtering data. You're lost on your own here, but having too much rows is an easier state than having none.

Look at result data at what you don't want and either make the where clause more restrictive or change join types to be more restrictive again.

What data do you want to see from JourneyNumber = 72732 and OrderIDs (3338854,3338847,3338787)?

If you mainly want to see head data of about 50 journey lines, then you can't join detil data at all, detail data always repeats head data the number of detail lines times.
Do you see 54 groups of interest with some combination of Journeynumber and something else, then group by them and leave out details or SUM them or AVG, or COUNT or compuite whatever aggregate of them.

Bye, Olaf.

 

[Cpreston]

Hi

I adopted this query however I now have it working with the code below. I am not pushing on adding a customerdelivery table which at the moment I am some issues with but I will try to figure it out first but will open a new post if required. Thanks for the answers

SELECT     TOP (100) PERCENT JourneyHeader.JourneyDate, CONVERT(varchar, JourneyHeader.JourneyNumber) AS JourneyNumber, JourneyLine.OrderID, 
                      JourneyHeader.NoOfDrops, JourneyzDropsByProduct.TotalVolume AS m3, dbo.Vehicle.udfVehicleGroup, 
                      dbo.Vehicle.Registration + ' - ' + dbo.Vehicle.Name AS VehicleName, JourneyzDropsByProduct.ProductCode, ProductGroup_1.Name AS [Group], 
                      ProductGroup.Name AS [Sub-group], Customer.CustomerCode, Customer.Name, Customer.City, dbo.ProductPack.PackRef
FROM         dbo.ProductPack INNER JOIN
                      dbo.OrderLineItem ON dbo.ProductPack.PackID = dbo.OrderLineItem.PackID RIGHT OUTER JOIN
                      dbo.JourneyLine AS JourneyLine INNER JOIN
                      dbo.JourneyHeader AS JourneyHeader ON JourneyLine.JourneyID = JourneyHeader.JourneyID INNER JOIN
                      dbo.JourneyzDropsByProduct AS JourneyzDropsByProduct ON JourneyHeader.JourneyID = JourneyzDropsByProduct.JourneyID AND 
                      JourneyLine.JourneyLineID = JourneyzDropsByProduct.JourneyLineID INNER JOIN
                      dbo.Product AS Product ON JourneyzDropsByProduct.ProductID = Product.ProductID INNER JOIN
                      dbo.ProductGroup AS ProductGroup ON Product.ProductGroupID = ProductGroup.ProductGroupID INNER JOIN
                      dbo.OrderHeader AS OrderHeader ON JourneyLine.OrderID = OrderHeader.OrderID AND JourneyHeader.JourneyID = OrderHeader.JourneyID INNER JOIN
                      dbo.Customer AS Customer ON OrderHeader.CustomerID = Customer.CustomerID INNER JOIN
                      dbo.ProductGroup AS ProductGroup_1 ON ProductGroup.ParentID = ProductGroup_1.ProductGroupID INNER JOIN
                      dbo.Vehicle ON JourneyHeader.VehicleID = dbo.Vehicle.VehicleID INNER JOIN
                      dbo.OrderLine ON OrderHeader.OrderID = dbo.OrderLine.OrderID AND Product.ProductID = dbo.OrderLine.ProductID ON 
                      dbo.OrderLineItem.OrderLineID = dbo.OrderLine.OrderLineID
WHERE     (JourneyHeader.JourneyNumber = 72821) AND (JourneyLine.OrderID = 3339201)
ORDER BY JourneyNumber DESC