Main Board > ODBC

join left and right join tables

(1/1)

eldonfsr:
Hi i need some body explain a lite bit about join left and right i trying to execute select query i read manuals and supost syntax is like that..

select * from { OJ IM1_inventoryMasterFile  full outer join AP1_VendorMaster  left outer join IMA_ProductLine   on IMA_ProductLine.ProductLineCode=IM1_inventoryMasterFile.productLine on AP1_VendorMaster.VendorNumber=IM1_inventoryMasterFile.PrimaryVendorNumber  } where IM1_inventoryMasterFile.itemnumber=:pprod 

what i need is get inventorymaterfile get all information and link Ima_productLineCode to get description of product line and from ap1_vendorMaster get vendor name , but send me error productline column not found.. this column is on inventorymasterfile..... what is rigth syntax to get access the imnformation...

Devon Austen:
Some descriptions of the different joins are found here: https://manual.pvxplus.com/?odbc/using_odbc_driver.htm
Some examples are foudn here: https://manual.pvxplus.com/?odbc/using_odbc_driver/example_sql.htm

My guess is this is what you want.

select * from { OJ (IM1_inventoryMasterFile  left outer join IMA_ProductLine   on IMA_ProductLine.ProductLineCode=IM1_inventoryMasterFile.productLine)  left outer join AP1_VendorMaster  on AP1_VendorMaster.VendorNumber=IM1_inventoryMasterFile.PrimaryVendorNumber  } where IM1_inventoryMasterFile.itemnumber=:pprod

Not that if you are using the PxPlus SQL ODBC driver version 6 or higher you can simplify  the syntax to remove the { oJ }

select * from (IM1_inventoryMasterFile  left outer join IMA_ProductLine   on IMA_ProductLine.ProductLineCode=IM1_inventoryMasterFile.productLine)  left outer join AP1_VendorMaster  on AP1_VendorMaster.VendorNumber=IM1_inventoryMasterFile.PrimaryVendorNumber where IM1_inventoryMasterFile.itemnumber=:pprod

Navigation

[0] Message Index