After running into problems, I’ve found in this Forum why it is not possible to use LEFT JOIN in a TSQL query that uses C/ODBC for connecting: It is just not supported (C/ODBC for navision 2.6) I’m using the query from inside another application (a documentation database), and therefore need to define the query in there. By the way: We are using the Native database (not SQL Server). So are there anybodyout there who has put together a query that does this: For a productnumber; - return from the products table: Description, vendor number - PLUS return the Vendor name from the vendor table, This is easy as long as every product record has a vendor number. I just use this (simplified example):
SELECT Prod.No, Prod.Descript, Prod.VendorNo, Prod.price, Vendor.name
FROM Prod, Vendor WHERE Vendor.no = Prod.VendorNo AND Prod.no = '216249'
But if a product record has a BLANK vendor number (maybe not decided yet!) the above query returns nothing - which is quite logic. Using MS Access with linked tables into navision, this Access query proved successful:
SELECT Prod.no, Prod.Descript, Vendor.name FROM Prod LEFT JOIN Vendor
ON prod.vendorNo = Vendor.no WHERE Prod.no = "216249"
So, does anyone have a good idea on how to achieve the LEFT JOIN results when C/ODBC doesn’t allow LEFT JOIN ? Arild