SQL Syntax

[NotSQL]

Hi Guys,

I Wonder if you could help. I need to join these too queries together and wonder how. I've given it a go but it just errors.

SELECT
INVOICE_REGISTER_PO.PurchaseOrder, INVOICE_REGISTER_PO.RegisterNumber, POHMAST.BuyerCode,
[SCM Database].dbo.vHLWPurchaseOrderBuyerCode.M685Buyer, ISNULL(POHMAST.BuyerCode,[SCM Database].dbo.vHLWPurchaseOrderBuyerCode.M685Buyer) AS OBuyerCode, A_P_SECTIONS.Currency

FROM
INVOICE_REGISTER_PO LEFT OUTER JOIN
VENDOR LEFT OUTER JOIN A_P_SECTIONS ON VENDOR.Section = A_P_SECTIONS.Section RIGHT OUTER JOIN INVOICE_REGISTER ON VENDOR.Supplier = INVOICE_REGISTER.Supplier ON INVOICE_REGISTER_PO.RegisterNumber = INVOICE_REGISTER.RegisterNumber LEFT OUTER JOIN POHMAST ON 'P*' + INVOICE_REGISTER_PO.PurchaseOrder = POHMAST.[Key] LEFT OUTER JOIN [SCM Database].dbo.vHLWPurchaseOrderBuyerCode ON INVOICE_REGISTER_PO.PurchaseOrder = [SCM Database].dbo.vHLWPurchaseOrderBuyerCode.PurchaseOrder
WHERE (INVOICE_REGISTER.MultiBusinessSuffix = 'A') AND (INVOICE_REGISTER_PO.PurchaseOrder <> 'N')


I want the NAMEfrom the result set below. It would join on OBuyerCode above to PO_BUYER_CODE.CODE below

Select PO_BUYER_CODE.CODE,PO_BUYER_CODE.[Name]
From dbo.PO_BUYER_CODE

 

[NotSQL]

I guess i could do:

Select *, (Select PO_BUYER_CODE.[Name]
From dbo.PO_BUYER_CODE where PO_BUYER_CODE.CODE = t1.OBuyerCOde) as Name
From(
SELECT
INVOICE_REGISTER_PO.PurchaseOrder, INVOICE_REGISTER_PO.RegisterNumber, POHMAST.BuyerCode,
[SCM Database].dbo.vHLWPurchaseOrderBuyerCode.M685Buyer, ISNULL(POHMAST.BuyerCode,[SCM Database].dbo.vHLWPurchaseOrderBuyerCode.M685Buyer) AS OBuyerCode, A_P_SECTIONS.Currency

FROM
INVOICE_REGISTER_PO LEFT OUTER JOIN
VENDOR LEFT OUTER JOIN A_P_SECTIONS ON VENDOR.Section = A_P_SECTIONS.Section RIGHT OUTER JOIN INVOICE_REGISTER ON VENDOR.Supplier = INVOICE_REGISTER.Supplier ON INVOICE_REGISTER_PO.RegisterNumber = INVOICE_REGISTER.RegisterNumber LEFT OUTER JOIN POHMAST ON 'P*' + INVOICE_REGISTER_PO.PurchaseOrder = POHMAST.[Key] LEFT OUTER JOIN [SCM Database].dbo.vHLWPurchaseOrderBuyerCode ON INVOICE_REGISTER_PO.PurchaseOrder = [SCM Database].dbo.vHLWPurchaseOrderBuyerCode.PurchaseOrder
WHERE (INVOICE_REGISTER.MultiBusinessSuffix = 'A') AND (INVOICE_REGISTER_PO.PurchaseOrder <> 'N'))t1

 

[r937]

the first thing i would do is fix your FROM clause --

  FROM INVOICE_REGISTER_PO 
LEFT OUTER
  JOIN VENDOR 
LEFT OUTER
  JOIN A_P_SECTIONS 
    ON VENDOR.Section = A_P_SECTIONS.Section 
RIGHT OUTER 
  JOIN INVOICE_REGISTER 
    ON VENDOR.Supplier = INVOICE_REGISTER.Supplier 
    ON INVOICE_REGISTER_PO.RegisterNumber 
     = INVOICE_REGISTER.RegisterNumber 
LEFT OUTER
  JOIN POHMAST 
    ON 'P*' + INVOICE_REGISTER_PO.PurchaseOrder = POHMAST.[Key] 
LEFT OUTER
  JOIN [SCM Database].dbo.vHLWPurchaseOrderBuyerCode 
    ON INVOICE_REGISTER_PO.PurchaseOrder 
     = [SCM Database].dbo.vHLWPurchaseOrderBuyerCode.PurchaseOrder

recommended improvements:

1. put the ON clause together with the table being joined

2. don't mix LEFT and RIGHT joins

seriously, this is of utmost importance when it comes to understanding and maintaining a mutli-table query

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon