INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Cannot get full result from Joins

Cannot get full result from Joins

(OP)
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

CODE --> sql

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 

RE: Cannot get full result from Joins

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

RE: Cannot get full result from Joins

(OP)
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
dbo.OrderLineItem ON OrderHeader.OrderID = dbo.OrderLineItem.Orde

RE: Cannot get full result from Joins

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.


RE: Cannot get full result from Joins

(OP)
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

CODE --> sql

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 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close