For the past two days i've been messing about with a single query. I've tried to dissect it's individual parts in a myriad of ways and have discovered the query only breaks when i add in the LEFT OUTER JOINs. I've tried in many different ways to even have one of my reference tables joined to some of the tables I want to use in my actual statement, but thus far to no avail.
(Note; Column selector has been kept to "*" for readability, there's a ton of rows here normally. Behaviour is the same when I test with the * selector.)
My full query:
SELECT *
FROM SO_SalesOrder so
INNER JOIN SO_Leg as l ON l.SalesOrder = so.SalesOrdernr
INNER JOIN SO_Cargo cg ON cg.Cargonr = l.Cargo
INNER JOIN SO_Activity ea ON ea.Activitynr = l.EndActivity
INNER JOIN SO_Activity ba ON ba.Activitynr = l.BeginActivity
LEFT OUTER JOIN RP_TripActivity eta ON eta.Activity = l.EndActivity
LEFT OUTER JOIN Product p ON p.Productnr = cg.Product
WHERE ba.Date BETWEEN '2015-10-01 00:00:00' AND '2015-10-07 23:59:59'
AND OrderStatus != 2
AND so.Customer = 95;
This database has been transferred from a MS-SQL server, hence i'm rewriting the queries to fit this change. The old query (still) works and used to be as follows:
SELECT *
FROM dbo.SO_SalesOrder as so
INNER JOIN dbo.SO_Leg as l ON l.SalesOrder = so.SalesOrdernr
INNER JOIN dbo.SO_Cargo AS cg ON cg.Cargonr = l.Cargo
INNER JOIN dbo.SO_Activity AS ea ON ea.Activitynr = l.EndActivity
INNER JOIN dbo.SO_Activity AS ba ON ba.Activitynr = l.BeginActivity
LEFT OUTER JOIN dbo.RP_TripActivity AS eta ON eta.Activity = l.EndActivity
LEFT OUTER JOIN dbo.RP_Trip AS et ON et.Tripnr = eta.Trip
LEFT OUTER JOIN dbo.RP_ResourceCombination AS erc ON erc.ResourceCombinationnr = eta.ResourceCombination
LEFT OUTER JOIN dbo.RP_Resource AS er1 ON er1.Resourcenr = erc.Truck
LEFT OUTER JOIN dbo.RP_ResourceCompany AS erc1 ON erc1.Resource = er1.Resourcenr AND erc1.Company = et.Company
LEFT OUTER JOIN dbo.VM_Vehicle AS ev1 ON ev1.Vehiclenr = er1.Vehicle
LEFT OUTER JOIN dbo.RP_Resource AS er3 ON er3.Resourcenr = erc.Driver
LEFT OUTER JOIN dbo.HR_Employee AS eemp3 ON eemp3.Employeenr = er3.Employee
LEFT OUTER JOIN dbo.Product AS p ON p.Productnr = cg.Product
WHERE ba.Date BETWEEN '2015-10-01 00:00:00'
AND '2015-10-07 23:59:59'
AND OrderStatus != 2
AND so.Customer = 95;
Would anyone perhaps have any pointers as to where I might screwing up?
Things i've tried: Removing WHERE-Clauses, adding "AS" keywords (as also done in MSSQL), Attempting a single left outer join and a single inner join on one table.
Edit: It might be worth noting this would be the first time I'm using MySQL instead of MSSQL/T-SQL, I have ofcourse tried to look up other examples, but this hasn't helped me thus far.
