Skip to Content
0
Jan 14, 2021 at 08:50 PM

Linking Tables with joins from SQL query

38 Views

I need to change the table links on a report I am editing.

I viewed the original report SQL and edited that to what I am needing but need to update the table links to match the query I am needing.

Here is the original SQL.

SELECT "Shipments"."smpShipmentID", "ShipmentLines"."smlUnitOfMeasure", "Shipments"."smpCustomerOrganizationID", "ShippingMethods"."xasDescription", "SalesOrders"."ompFreeOnBoardDescription", "SalesOrders"."ompCustomerPO", "SalesOrderLines"."omlOrderQuantity", "ShipmentLines"."smlSalesOrderID", "SalesOrderLines"."omlQuantityShipped", "ShipmentLines"."smlShippedComplete", "ShipmentLines"."smlSalesOrderLineID", "ShipmentLines"."smlSalesOrderDeliveryID", "ShipmentLines"."smlShipmentLineID", "Shipments"."smpARInvoiceLocationID", "Shipments"."smpShipLocationID", "Shipments"."smpShipOrganizationID", "ShipmentLines"."smlUniqueID", "SalesOrders"."ompSalesOrderID", "SalesOrderLines"."omlPartID", "SalesOrderLines"."omlPartRevisionID", "SalesOrderLines"."omlPartShortDescription", "SalesOrderLines"."omlSalesOrderLineID", "SalesOrderLines"."uomlCustPOLine", "SalesOrderLines"."omlOrgPartID", "SalesOrderLines"."omlOrgPartShortDescription", "Shipments"."usmpPallets", "Shipments"."usmpCartons", "SalesOrders"."ompOrderDate", "Shipments"."smpTrackingNumber", "Projects"."uprpPrimeContractNumber", "Shipments"."smpShipDate", "Projects"."uprpDPASRating", "Organizations"."cmoOrganizationID", "Projects"."uprpITAR", "ShipmentLines"."smlWeightUnitOfMeasure", "Shipments"."usmpActualWeight", "ShipmentLines"."smlQuantityShipped", "SalesOrderDeliveries"."omdDeliveryDate", ({fn ifnull((select cmcName from m1_m1.dbo."OrganizationContacts" "OrganizationContacts" where OrganizationContacts.cmcOrganizationID=Shipments.smpCustomerOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpARInvoiceLocationID and OrganizationContacts.cmcContactID = Shipments.smpARInvoiceContactID),'')}), ({fn ifnull((select Count(*) from m1_m1.dbo."SerialNumberTransactions" "SerialNumberTransactions" where "ShipmentLines"."smlUniqueID" = "SerialNumberTransactions"."sntTableUniqueID"),0)}), ({fn ifnull((select cmcFaxNumber from m1_m1.dbo."OrganizationContacts" "OrganizationContacts" where OrganizationContacts.cmcOrganizationID=Shipments.smpCustomerOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpARInvoiceLocationID and OrganizationContacts.cmcContactID = Shipments.smpARInvoiceContactID),'')}), ({fn ifnull((select cmcPhoneNumber from m1_m1.dbo."OrganizationContacts" "OrganizationContacts" where OrganizationContacts.cmcOrganizationID=Shipments.smpCustomerOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpARInvoiceLocationID and OrganizationContacts.cmcContactID = Shipments.smpARInvoiceContactID),'')}), ({fn ifnull((select Count(*) from m1_m1.dbo."LotNumberTransactions" "LotNumberTransactions" where "ShipmentLines"."smlUniqueID" = "LotNumberTransactions"."abtTableUniqueID"),0)}), ({fn ifnull((select cmcName from m1_m1.dbo."OrganizationContacts" "OrganizationContacts" where OrganizationContacts.cmcOrganizationID=Shipments.smpShipOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpShipLocationID and OrganizationContacts.cmcContactID = Shipments.smpShipContactID),'')}), ({fn ifnull((select cmcFaxNumber from m1_m1.dbo."OrganizationContacts" "OrganizationContacts" where OrganizationContacts.cmcOrganizationID=Shipments.smpShipOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpShipLocationID and OrganizationContacts.cmcContactID = Shipments.smpShipContactID),'')}), ({fn ifnull((select cmcPhoneNumber from m1_m1.dbo."OrganizationContacts" "OrganizationContacts" where OrganizationContacts.cmcOrganizationID=Shipments.smpShipOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpShipLocationID and OrganizationContacts.cmcContactID = Shipments.smpShipContactID),'')}), "ShipmentLines"."smlPartLongDescriptionRTF", "SalesOrders"."ompOrderCommentsText", "Shipments"."smpShippingCommentsText", "SalesOrders"."ompShippingInstructionsText", "Organizations"."cmoShippingInstructionsText"FROM   ((((((("M1_M1"."dbo"."Shipments" "Shipments" INNER JOIN "M1_M1"."dbo"."ShipmentLines" "ShipmentLines" ON "Shipments"."smpShipmentID"="ShipmentLines"."smlShipmentID") LEFT OUTER JOIN "M1_M1"."dbo"."ShippingMethods" "ShippingMethods" ON "Shipments"."smpShippingMethodID"="ShippingMethods"."xasShippingMethodID") LEFT OUTER JOIN "M1_M1"."dbo"."OrganizationContacts" "OrganizationContacts" ON ((("Shipments"."smpShipLocationID"="OrganizationContacts"."cmcLocationID") AND ("Shipments"."smpShipContactID"="OrganizationContacts"."cmcContactID")) AND ("Shipments"."smpShipOrganizationID"="OrganizationContacts"."cmcOrganizationID")) AND ("Shipments"."smpARInvoiceLocationID"="OrganizationContacts"."cmcContactID")) LEFT OUTER JOIN "M1_M1"."dbo"."Organizations" "Organizations" ON "Shipments"."smpCustomerOrganizationID"="Organizations"."cmoOrganizationID") LEFT OUTER JOIN "M1_M1"."dbo"."Projects" "Projects" ON "Shipments"."smpProjectID"="Projects"."prpProjectID") LEFT OUTER JOIN "M1_M1"."dbo"."SalesOrders" "SalesOrders" ON "ShipmentLines"."smlSalesOrderID"="SalesOrders"."ompSalesOrderID") LEFT OUTER JOIN "M1_M1"."dbo"."SalesOrderLines" "SalesOrderLines" ON ("ShipmentLines"."smlSalesOrderID"="SalesOrderLines"."omlSalesOrderID") AND ("ShipmentLines"."smlSalesOrderLineID"="SalesOrderLines"."omlSalesOrderLineID")) LEFT OUTER JOIN "M1_M1"."dbo"."SalesOrderDeliveries" "SalesOrderDeliveries" ON ("SalesOrderLines"."omlSalesOrderID"="SalesOrderDeliveries"."omdSalesOrderID") AND ("SalesOrderLines"."omlSalesOrderLineID"="SalesOrderDeliveries"."omdSalesOrderLineID")ORDER BY "Shipments"."smpShipmentID", "ShipmentLines"."smlSalesOrderID", "ShipmentLines"."smlSalesOrderLineID", "ShipmentLines"."smlSalesOrderDeliveryID", "ShipmentLines"."smlShipmentLineID"

And here is what I want the sql to be after changing the table links.

 SELECT "Shipments"."smpShipmentID", "ShipmentLines"."smlUnitOfMeasure", "Shipments"."smpCustomerOrganizationID", "ShippingMethods"."xasDescription", "SalesOrders"."ompFreeOnBoardDescription", "SalesOrders"."ompCustomerPO", "SalesOrderLines"."omlOrderQuantity", "ShipmentLines"."smlSalesOrderID", "SalesOrderLines"."omlQuantityShipped", "ShipmentLines"."smlShippedComplete", "ShipmentLines"."smlSalesOrderLineID", "ShipmentLines"."smlSalesOrderDeliveryID", "ShipmentLines"."smlShipmentLineID", "Shipments"."smpARInvoiceLocationID", "Shipments"."smpShipLocationID", "Shipments"."smpShipOrganizationID", "ShipmentLines"."smlUniqueID", "SalesOrders"."ompSalesOrderID", "SalesOrderLines"."omlPartID", "SalesOrderLines"."omlPartRevisionID", "SalesOrderLines"."omlPartShortDescription", "SalesOrderLines"."omlSalesOrderLineID", "SalesOrderLines"."uomlCustPOLine", "SalesOrderLines"."omlOrgPartID", "SalesOrderLines"."omlOrgPartShortDescription", "Shipments"."usmpPallets", "Shipments"."usmpCartons", "SalesOrders"."ompOrderDate", "Shipments"."smpTrackingNumber", "Projects"."uprpPrimeContractNumber", "Shipments"."smpShipDate", "Projects"."uprpDPASRating", "Organizations"."cmoOrganizationID", "Projects"."uprpITAR", "ShipmentLines"."smlWeightUnitOfMeasure", "Shipments"."usmpActualWeight", "ShipmentLines"."smlQuantityShipped", "SalesOrderDeliveries"."omdDeliveryDate", ({fn ifnull((select cmcName from m1_m1.dbo."OrganizationContacts" "OrganizationContacts" where OrganizationContacts.cmcOrganizationID=Shipments.smpCustomerOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpARInvoiceLocationID and OrganizationContacts.cmcContactID = Shipments.smpARInvoiceContactID),'')}), ({fn ifnull((select Count(*) from m1_m1.dbo."SerialNumberTransactions" "SerialNumberTransactions" where "ShipmentLines"."smlUniqueID" = "SerialNumberTransactions"."sntTableUniqueID"),0)}), ({fn ifnull((select cmcFaxNumber from m1_m1.dbo."OrganizationContacts" "OrganizationContacts" where OrganizationContacts.cmcOrganizationID=Shipments.smpCustomerOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpARInvoiceLocationID and OrganizationContacts.cmcContactID = Shipments.smpARInvoiceContactID),'')}), ({fn ifnull((select cmcPhoneNumber from m1_m1.dbo."OrganizationContacts" "OrganizationContacts" where OrganizationContacts.cmcOrganizationID=Shipments.smpCustomerOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpARInvoiceLocationID and OrganizationContacts.cmcContactID = Shipments.smpARInvoiceContactID),'')}), ({fn ifnull((select Count(*) from m1_m1.dbo."LotNumberTransactions" "LotNumberTransactions" where "ShipmentLines"."smlUniqueID" = "LotNumberTransactions"."abtTableUniqueID"),0)}), ({fn ifnull((select cmcName from m1_m1.dbo."OrganizationContacts" "OrganizationContacts" where OrganizationContacts.cmcOrganizationID=Shipments.smpShipOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpShipLocationID and OrganizationContacts.cmcContactID = Shipments.smpShipContactID),'')}), ({fn ifnull((select cmcFaxNumber from m1_m1.dbo."OrganizationContacts" "OrganizationContacts" where OrganizationContacts.cmcOrganizationID=Shipments.smpShipOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpShipLocationID and OrganizationContacts.cmcContactID = Shipments.smpShipContactID),'')}), ({fn ifnull((select cmcPhoneNumber from m1_m1.dbo."OrganizationContacts" "OrganizationContacts" where OrganizationContacts.cmcOrganizationID=Shipments.smpShipOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpShipLocationID and OrganizationContacts.cmcContactID = Shipments.smpShipContactID),'')}), "ShipmentLines"."smlPartLongDescriptionRTF", "SalesOrders"."ompOrderCommentsText", "Shipments"."smpShippingCommentsText", "SalesOrders"."ompShippingInstructionsText", "Organizations"."cmoShippingInstructionsText" FROM ((((((("M1_L1"."dbo"."Shipments" "Shipments" INNER JOIN "M1_L1"."dbo"."ShipmentLines" "ShipmentLines" ON "Shipments"."smpShipmentID"="ShipmentLines"."smlShipmentID") INNER JOIN "M1_L1"."dbo"."ShippingMethods" "ShippingMethods" ON "Shipments"."smpShippingMethodID"="ShippingMethods"."xasShippingMethodID") LEFT OUTER JOIN "M1_L1"."dbo"."OrganizationContacts" "OrganizationContacts" ON ((("Shipments"."smpShipLocationID"="OrganizationContacts"."cmcLocationID") AND ("Shipments"."smpShipContactID"="OrganizationContacts"."cmcContactID")) AND ("Shipments"."smpShipOrganizationID"="OrganizationContacts"."cmcOrganizationID")) AND ("Shipments"."smpARInvoiceLocationID"="OrganizationContacts"."cmcContactID")) LEFT OUTER JOIN "M1_L1"."dbo"."Organizations" "Organizations" ON "Shipments"."smpCustomerOrganizationID"="Organizations"."cmoOrganizationID") LEFT OUTER JOIN "M1_L1"."dbo"."Projects" "Projects" ON "Shipments"."smpProjectID"="Projects"."prpProjectID") LEFT OUTER JOIN "M1_L1"."dbo"."SalesOrders" "SalesOrders" ON "ShipmentLines"."smlSalesOrderID"="SalesOrders"."ompSalesOrderID") INNER JOIN "M1_L1"."dbo"."SalesOrderLines" "SalesOrderLines" ON ("ShipmentLines"."smlSalesOrderID"="SalesOrderLines"."omlSalesOrderID") AND ("ShipmentLines"."smlSalesOrderLineID"="SalesOrderLines"."omlSalesOrderLineID")) INNER JOIN "M1_L1"."dbo"."SalesOrderDeliveries" "SalesOrderDeliveries" ON ("SalesOrderLines"."omlSalesOrderID"="SalesOrderDeliveries"."omdSalesOrderID")And ("ShipmentLines"."smlSalesOrderDeliveryID"="SalesOrderDeliveries"."omdSalesOrderDeliveryID")WHERE  "Shipments"."smpShipmentID"='16817'ORDER BY "Shipments"."smpShipmentID", "ShipmentLines"."smlSalesOrderID", "ShipmentLines"."smlSalesOrderLineID", "ShipmentLines"."smlSalesOrderDeliveryID", "ShipmentLines"."smlShipmentLineID"