title: Linking tables and table joins in crystal reports
body: <p>I have pulled the SQL from a report and need to edit it. I understand I can edit the links in the database expert but I am getting different results.
  Below is the query in the report that is currently setup but I want to change:</p>
  <pre><code>  SELECT &quot;Shipments&quot;.&quot;smpShipmentID&quot;, &quot;ShipmentLines&quot;.&quot;smlUnitOfMeasure&quot;, &quot;Shipments&quot;.&quot;smpCustomerOrganizationID&quot;, &quot;ShippingMethods&quot;.&quot;xasDescription&quot;, &quot;SalesOrders&quot;.&quot;ompFreeOnBoardDescription&quot;, &quot;SalesOrders&quot;.&quot;ompCustomerPO&quot;, &quot;SalesOrderLines&quot;.&quot;omlOrderQuantity&quot;, &quot;ShipmentLines&quot;.&quot;smlSalesOrderID&quot;, &quot;SalesOrderLines&quot;.&quot;omlQuantityShipped&quot;, &quot;ShipmentLines&quot;.&quot;smlShippedComplete&quot;, &quot;ShipmentLines&quot;.&quot;smlSalesOrderLineID&quot;, &quot;ShipmentLines&quot;.&quot;smlSalesOrderDeliveryID&quot;, &quot;ShipmentLines&quot;.&quot;smlShipmentLineID&quot;, &quot;Shipments&quot;.&quot;smpARInvoiceLocationID&quot;, &quot;Shipments&quot;.&quot;smpShipLocationID&quot;, &quot;Shipments&quot;.&quot;smpShipOrganizationID&quot;, &quot;ShipmentLines&quot;.&quot;smlUniqueID&quot;, &quot;SalesOrders&quot;.&quot;ompSalesOrderID&quot;, &quot;SalesOrderLines&quot;.&quot;omlPartID&quot;, &quot;SalesOrderLines&quot;.&quot;omlPartRevisionID&quot;, &quot;SalesOrderLines&quot;.&quot;omlPartShortDescription&quot;, &quot;SalesOrderLines&quot;.&quot;omlSalesOrderLineID&quot;, &quot;SalesOrderLines&quot;.&quot;uomlCustPOLine&quot;, &quot;SalesOrderLines&quot;.&quot;omlOrgPartID&quot;, &quot;SalesOrderLines&quot;.&quot;omlOrgPartShortDescription&quot;, &quot;Shipments&quot;.&quot;usmpPallets&quot;, &quot;Shipments&quot;.&quot;usmpCartons&quot;, &quot;SalesOrders&quot;.&quot;ompOrderDate&quot;, &quot;Shipments&quot;.&quot;smpTrackingNumber&quot;, &quot;Projects&quot;.&quot;uprpPrimeContractNumber&quot;, &quot;Shipments&quot;.&quot;smpShipDate&quot;, &quot;Projects&quot;.&quot;uprpDPASRating&quot;, &quot;Organizations&quot;.&quot;cmoOrganizationID&quot;, &quot;Projects&quot;.&quot;uprpITAR&quot;, &quot;ShipmentLines&quot;.&quot;smlWeightUnitOfMeasure&quot;, &quot;Shipments&quot;.&quot;usmpActualWeight&quot;, &quot;ShipmentLines&quot;.&quot;smlQuantityShipped&quot;, &quot;SalesOrderDeliveries&quot;.&quot;omdDeliveryDate&quot;, ({fn ifnull((select cmcName from m1_l1.dbo.&quot;OrganizationContacts&quot; &quot;OrganizationContacts&quot; where OrganizationContacts.cmcOrganizationID=Shipments.smpCustomerOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpARInvoiceLocationID and OrganizationContacts.cmcContactID = Shipments.smpARInvoiceContactID),'')}), ({fn ifnull((select Count(*) from m1_l1.dbo.&quot;SerialNumberTransactions&quot; &quot;SerialNumberTransactions&quot; where &quot;ShipmentLines&quot;.&quot;smlUniqueID&quot; = &quot;SerialNumberTransactions&quot;.&quot;sntTableUniqueID&quot;),0)}), ({fn ifnull((select cmcFaxNumber from m1_l1.dbo.&quot;OrganizationContacts&quot; &quot;OrganizationContacts&quot; where OrganizationContacts.cmcOrganizationID=Shipments.smpCustomerOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpARInvoiceLocationID and OrganizationContacts.cmcContactID = Shipments.smpARInvoiceContactID),'')}), ({fn ifnull((select cmcPhoneNumber from m1_l1.dbo.&quot;OrganizationContacts&quot; &quot;OrganizationContacts&quot; where OrganizationContacts.cmcOrganizationID=Shipments.smpCustomerOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpARInvoiceLocationID and OrganizationContacts.cmcContactID = Shipments.smpARInvoiceContactID),'')}), ({fn ifnull((select Count(*) from m1_l1.dbo.&quot;LotNumberTransactions&quot; &quot;LotNumberTransactions&quot; where &quot;ShipmentLines&quot;.&quot;smlUniqueID&quot; = &quot;LotNumberTransactions&quot;.&quot;abtTableUniqueID&quot;),0)}), ({fn ifnull((select cmcName from m1_l1.dbo.&quot;OrganizationContacts&quot; &quot;OrganizationContacts&quot; where OrganizationContacts.cmcOrganizationID=Shipments.smpShipOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpShipLocationID and OrganizationContacts.cmcContactID = Shipments.smpShipContactID),'')}), ({fn ifnull((select cmcFaxNumber from m1_l1.dbo.&quot;OrganizationContacts&quot; &quot;OrganizationContacts&quot; where OrganizationContacts.cmcOrganizationID=Shipments.smpShipOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpShipLocationID and OrganizationContacts.cmcContactID = Shipments.smpShipContactID),'')}), ({fn ifnull((select cmcPhoneNumber from m1_l1.dbo.&quot;OrganizationContacts&quot; &quot;OrganizationContacts&quot; where OrganizationContacts.cmcOrganizationID=Shipments.smpShipOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpShipLocationID and OrganizationContacts.cmcContactID = Shipments.smpShipContactID),'')}), &quot;ShipmentLines&quot;.&quot;smlPartLongDescriptionRTF&quot;, &quot;SalesOrders&quot;.&quot;ompOrderCommentsText&quot;, &quot;Shipments&quot;.&quot;smpShippingCommentsText&quot;, &quot;SalesOrders&quot;.&quot;ompShippingInstructionsText&quot;, &quot;Organizations&quot;.&quot;cmoShippingInstructionsText&quot;FROM   (((((((&quot;m1_l1&quot;.&quot;dbo&quot;.&quot;Shipments&quot; &quot;Shipments&quot; INNER JOIN &quot;m1_l1&quot;.&quot;dbo&quot;.&quot;ShipmentLines&quot; &quot;ShipmentLines&quot; ON &quot;Shipments&quot;.&quot;smpShipmentID&quot;=&quot;ShipmentLines&quot;.&quot;smlShipmentID&quot;) LEFT OUTER JOIN &quot;m1_l1&quot;.&quot;dbo&quot;.&quot;ShippingMethods&quot; &quot;ShippingMethods&quot; ON &quot;Shipments&quot;.&quot;smpShippingMethodID&quot;=&quot;ShippingMethods&quot;.&quot;xasShippingMethodID&quot;) LEFT OUTER JOIN &quot;m1_l1&quot;.&quot;dbo&quot;.&quot;OrganizationContacts&quot; &quot;OrganizationContacts&quot; ON (((&quot;Shipments&quot;.&quot;smpShipLocationID&quot;=&quot;OrganizationContacts&quot;.&quot;cmcLocationID&quot;) AND (&quot;Shipments&quot;.&quot;smpShipContactID&quot;=&quot;OrganizationContacts&quot;.&quot;cmcContactID&quot;)) AND (&quot;Shipments&quot;.&quot;smpShipOrganizationID&quot;=&quot;OrganizationContacts&quot;.&quot;cmcOrganizationID&quot;) AND (&quot;Shipments&quot;.&quot;smpARInvoiceLocationID&quot;=&quot;OrganizationContacts&quot;.&quot;cmcContactID&quot;) LEFT OUTER JOIN &quot;m1_l1&quot;.&quot;dbo&quot;.&quot;Organizations&quot; &quot;Organizations&quot; ON &quot;Shipments&quot;.&quot;smpCustomerOrganizationID&quot;=&quot;Organizations&quot;.&quot;cmoOrganizationID&quot;) LEFT OUTER JOIN &quot;m1_l1&quot;.&quot;dbo&quot;.&quot;Projects&quot; &quot;Projects&quot; ON &quot;Shipments&quot;.&quot;smpProjectID&quot;=&quot;Projects&quot;.&quot;prpProjectID&quot;) LEFT OUTER JOIN &quot;m1_l1&quot;.&quot;dbo&quot;.&quot;SalesOrders&quot; &quot;SalesOrders&quot; ON &quot;ShipmentLines&quot;.&quot;smlSalesOrderID&quot;=&quot;SalesOrders&quot;.&quot;ompSalesOrderID&quot;) LEFT OUTER JOIN &quot;m1_l1&quot;.&quot;dbo&quot;.&quot;SalesOrderLines&quot; &quot;SalesOrderLines&quot; ON (&quot;ShipmentLines&quot;.&quot;smlSalesOrderID&quot;=&quot;SalesOrderLines&quot;.&quot;omlSalesOrderID&quot;) AND (&quot;ShipmentLines&quot;.&quot;smlSalesOrderLineID&quot;=&quot;SalesOrderLines&quot;.&quot;omlSalesOrderLineID&quot;)) LEFT OUTER JOIN &quot;m1_l1&quot;.&quot;dbo&quot;.&quot;SalesOrderDeliveries&quot; &quot;SalesOrderDeliveries&quot; ON (&quot;SalesOrderLines&quot;.&quot;omlSalesOrderID&quot;=&quot;SalesOrderDeliveries&quot;.&quot;omdSalesOrderID&quot;) AND (&quot;SalesOrderLines&quot;.&quot;omlSalesOrderLineID&quot;=&quot;SalesOrderDeliveries&quot;.&quot;omdSalesOrderLineID&quot;) WHERE  &quot;Shipments&quot;.&quot;smpShipmentID&quot;='16817' ORDER BY &quot;Shipments&quot;.&quot;smpShipmentID&quot;, &quot;ShipmentLines&quot;.&quot;smlSalesOrderID&quot;, &quot;ShipmentLines&quot;.&quot;smlSalesOrderLineID&quot;, &quot;ShipmentLines&quot;.&quot;smlSalesOrderDeliveryID&quot;, &quot;ShipmentLines&quot;.&quot;smlShipmentLineID&quot;
  </code></pre>
  <p>Here is the SQL that works which only has a different join ON but I am not sure how to make the changes in the database expert links setup.</p>
  <pre><code> SELECT &quot;Shipments&quot;.&quot;smpShipmentID&quot;, &quot;ShipmentLines&quot;.&quot;smlUnitOfMeasure&quot;, &quot;Shipments&quot;.&quot;smpCustomerOrganizationID&quot;, &quot;ShippingMethods&quot;.&quot;xasDescription&quot;, &quot;SalesOrders&quot;.&quot;ompFreeOnBoardDescription&quot;, &quot;SalesOrders&quot;.&quot;ompCustomerPO&quot;, &quot;SalesOrderLines&quot;.&quot;omlOrderQuantity&quot;, &quot;ShipmentLines&quot;.&quot;smlSalesOrderID&quot;, &quot;SalesOrderLines&quot;.&quot;omlQuantityShipped&quot;, &quot;ShipmentLines&quot;.&quot;smlShippedComplete&quot;, &quot;ShipmentLines&quot;.&quot;smlSalesOrderLineID&quot;, &quot;ShipmentLines&quot;.&quot;smlSalesOrderDeliveryID&quot;, &quot;ShipmentLines&quot;.&quot;smlShipmentLineID&quot;, &quot;Shipments&quot;.&quot;smpARInvoiceLocationID&quot;, &quot;Shipments&quot;.&quot;smpShipLocationID&quot;, &quot;Shipments&quot;.&quot;smpShipOrganizationID&quot;, &quot;ShipmentLines&quot;.&quot;smlUniqueID&quot;, &quot;SalesOrders&quot;.&quot;ompSalesOrderID&quot;, &quot;SalesOrderLines&quot;.&quot;omlPartID&quot;, &quot;SalesOrderLines&quot;.&quot;omlPartRevisionID&quot;, &quot;SalesOrderLines&quot;.&quot;omlPartShortDescription&quot;, &quot;SalesOrderLines&quot;.&quot;omlSalesOrderLineID&quot;, &quot;SalesOrderLines&quot;.&quot;uomlCustPOLine&quot;, &quot;SalesOrderLines&quot;.&quot;omlOrgPartID&quot;, &quot;SalesOrderLines&quot;.&quot;omlOrgPartShortDescription&quot;, &quot;Shipments&quot;.&quot;usmpPallets&quot;, &quot;Shipments&quot;.&quot;usmpCartons&quot;, &quot;SalesOrders&quot;.&quot;ompOrderDate&quot;, &quot;Shipments&quot;.&quot;smpTrackingNumber&quot;, &quot;Projects&quot;.&quot;uprpPrimeContractNumber&quot;, &quot;Shipments&quot;.&quot;smpShipDate&quot;, &quot;Projects&quot;.&quot;uprpDPASRating&quot;, &quot;Organizations&quot;.&quot;cmoOrganizationID&quot;, &quot;Projects&quot;.&quot;uprpITAR&quot;, &quot;ShipmentLines&quot;.&quot;smlWeightUnitOfMeasure&quot;, &quot;Shipments&quot;.&quot;usmpActualWeight&quot;, &quot;ShipmentLines&quot;.&quot;smlQuantityShipped&quot;, &quot;SalesOrderDeliveries&quot;.&quot;omdDeliveryDate&quot;, ({fn ifnull((select cmcName from m1_m1.dbo.&quot;OrganizationContacts&quot; &quot;OrganizationContacts&quot; where OrganizationContacts.cmcOrganizationID=Shipments.smpCustomerOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpARInvoiceLocationID and OrganizationContacts.cmcContactID = Shipments.smpARInvoiceContactID),'')}), ({fn ifnull((select Count(*) from m1_m1.dbo.&quot;SerialNumberTransactions&quot; &quot;SerialNumberTransactions&quot; where &quot;ShipmentLines&quot;.&quot;smlUniqueID&quot; = &quot;SerialNumberTransactions&quot;.&quot;sntTableUniqueID&quot;),0)}), ({fn ifnull((select cmcFaxNumber from m1_m1.dbo.&quot;OrganizationContacts&quot; &quot;OrganizationContacts&quot; where OrganizationContacts.cmcOrganizationID=Shipments.smpCustomerOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpARInvoiceLocationID and OrganizationContacts.cmcContactID = Shipments.smpARInvoiceContactID),'')}), ({fn ifnull((select cmcPhoneNumber from m1_m1.dbo.&quot;OrganizationContacts&quot; &quot;OrganizationContacts&quot; where OrganizationContacts.cmcOrganizationID=Shipments.smpCustomerOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpARInvoiceLocationID and OrganizationContacts.cmcContactID = Shipments.smpARInvoiceContactID),'')}), ({fn ifnull((select Count(*) from m1_m1.dbo.&quot;LotNumberTransactions&quot; &quot;LotNumberTransactions&quot; where &quot;ShipmentLines&quot;.&quot;smlUniqueID&quot; = &quot;LotNumberTransactions&quot;.&quot;abtTableUniqueID&quot;),0)}), ({fn ifnull((select cmcName from m1_m1.dbo.&quot;OrganizationContacts&quot; &quot;OrganizationContacts&quot; where OrganizationContacts.cmcOrganizationID=Shipments.smpShipOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpShipLocationID and OrganizationContacts.cmcContactID = Shipments.smpShipContactID),'')}), ({fn ifnull((select cmcFaxNumber from m1_m1.dbo.&quot;OrganizationContacts&quot; &quot;OrganizationContacts&quot; where OrganizationContacts.cmcOrganizationID=Shipments.smpShipOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpShipLocationID and OrganizationContacts.cmcContactID = Shipments.smpShipContactID),'')}), ({fn ifnull((select cmcPhoneNumber from m1_m1.dbo.&quot;OrganizationContacts&quot; &quot;OrganizationContacts&quot; where OrganizationContacts.cmcOrganizationID=Shipments.smpShipOrganizationID and OrganizationContacts.cmcLocationID = Shipments.smpShipLocationID and OrganizationContacts.cmcContactID = Shipments.smpShipContactID),'')}), &quot;ShipmentLines&quot;.&quot;smlPartLongDescriptionRTF&quot;, &quot;SalesOrders&quot;.&quot;ompOrderCommentsText&quot;, &quot;Shipments&quot;.&quot;smpShippingCommentsText&quot;, &quot;SalesOrders&quot;.&quot;ompShippingInstructionsText&quot;, &quot;Organizations&quot;.&quot;cmoShippingInstructionsText&quot; FROM (((((((&quot;M1_L1&quot;.&quot;dbo&quot;.&quot;Shipments&quot; &quot;Shipments&quot; INNER JOIN &quot;M1_L1&quot;.&quot;dbo&quot;.&quot;ShipmentLines&quot; &quot;ShipmentLines&quot; ON &quot;Shipments&quot;.&quot;smpShipmentID&quot;=&quot;ShipmentLines&quot;.&quot;smlShipmentID&quot;) INNER JOIN &quot;M1_L1&quot;.&quot;dbo&quot;.&quot;ShippingMethods&quot; &quot;ShippingMethods&quot; ON &quot;Shipments&quot;.&quot;smpShippingMethodID&quot;=&quot;ShippingMethods&quot;.&quot;xasShippingMethodID&quot;) LEFT OUTER JOIN &quot;M1_L1&quot;.&quot;dbo&quot;.&quot;OrganizationContacts&quot; &quot;OrganizationContacts&quot; ON (((&quot;Shipments&quot;.&quot;smpShipLocationID&quot;=&quot;OrganizationContacts&quot;.&quot;cmcLocationID&quot;) AND (&quot;Shipments&quot;.&quot;smpShipContactID&quot;=&quot;OrganizationContacts&quot;.&quot;cmcContactID&quot;)) AND (&quot;Shipments&quot;.&quot;smpShipOrganizationID&quot;=&quot;OrganizationContacts&quot;.&quot;cmcOrganizationID&quot;)) AND (&quot;Shipments&quot;.&quot;smpARInvoiceLocationID&quot;=&quot;OrganizationContacts&quot;.&quot;cmcContactID&quot;)) LEFT OUTER JOIN &quot;M1_L1&quot;.&quot;dbo&quot;.&quot;Organizations&quot; &quot;Organizations&quot; ON &quot;Shipments&quot;.&quot;smpCustomerOrganizationID&quot;=&quot;Organizations&quot;.&quot;cmoOrganizationID&quot;) LEFT OUTER JOIN &quot;M1_L1&quot;.&quot;dbo&quot;.&quot;Projects&quot; &quot;Projects&quot; ON &quot;Shipments&quot;.&quot;smpProjectID&quot;=&quot;Projects&quot;.&quot;prpProjectID&quot;) LEFT OUTER JOIN &quot;M1_L1&quot;.&quot;dbo&quot;.&quot;SalesOrders&quot; &quot;SalesOrders&quot; ON &quot;ShipmentLines&quot;.&quot;smlSalesOrderID&quot;=&quot;SalesOrders&quot;.&quot;ompSalesOrderID&quot;) INNER JOIN &quot;M1_L1&quot;.&quot;dbo&quot;.&quot;SalesOrderLines&quot; &quot;SalesOrderLines&quot; ON (&quot;ShipmentLines&quot;.&quot;smlSalesOrderID&quot;=&quot;SalesOrderLines&quot;.&quot;omlSalesOrderID&quot;) AND (&quot;ShipmentLines&quot;.&quot;smlSalesOrderLineID&quot;=&quot;SalesOrderLines&quot;.&quot;omlSalesOrderLineID&quot;)) INNER JOIN &quot;M1_L1&quot;.&quot;dbo&quot;.&quot;SalesOrderDeliveries&quot; &quot;SalesOrderDeliveries&quot; ON (&quot;SalesOrderLines&quot;.&quot;omlSalesOrderID&quot;=&quot;SalesOrderDeliveries&quot;.&quot;omdSalesOrderID&quot;)And (&quot;ShipmentLines&quot;.&quot;smlSalesOrderDeliveryID&quot;=&quot;SalesOrderDeliveries&quot;.&quot;omdSalesOrderDeliveryID&quot;)WHERE  &quot;Shipments&quot;.&quot;smpShipmentID&quot;='16817'ORDER BY &quot;Shipments&quot;.&quot;smpShipmentID&quot;, &quot;ShipmentLines&quot;.&quot;smlSalesOrderID&quot;, &quot;ShipmentLines&quot;.&quot;smlSalesOrderLineID&quot;, &quot;ShipmentLines&quot;.&quot;smlSalesOrderDeliveryID&quot;, &quot;ShipmentLines&quot;.&quot;smlShipmentLineID&quot;
  </code></pre>
  <p>I tried adding via Command but it breaks other aspects of my report and I am not sure the client program will be able to send the correct parameters to the report using command.</p>
  <p>Please help!</p>