PxPlus User Forum

Twitter Twitter Twitter

Author Topic: join left and right join tables  (Read 658 times)

eldonfsr

  • Silver Member
  • ***
  • Posts: 39
    • View Profile
join left and right join tables
« on: December 30, 2022, 07:11:38 PM »
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

  • Administrator
  • Diamond Member
  • *****
  • Posts: 382
  • Don’t Panic
    • View Profile
    • PVX Plus Technologies
Re: join left and right join tables
« Reply #1 on: January 09, 2023, 08:54:10 AM »
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

Principal Software Engineer for PVX Plus Technologies LTD.