title: Merge and UNION records from two tables using JOIN
body: <p>How can i merge and <code>UNION</code> records from two tables using <code>JOIN</code>. For example let say i have the following Table A and B, and want to end up like Table C. </p>
  
  <p>I want to extract and merge all <code>tableB.Item ID</code> which matches <code>tableA.Customer ID</code> and if there is more than one match then i want to create a new fields which holds the other. </p>
  
  <p>//Table A</p>
  
  <pre><code>  Customer ID     Customer_Bill_Name.......                  
      XXX001           XXX Company Name......           
      XXX002           YYY Company Name......         
  </code></pre>
  
  <p>//Table B</p>
  
  <pre><code> Customer ID          Item ID 1           
       XXX001            WH15 02
       XXX001            600278        
       XX0002            600000      
  </code></pre>
  
  <p>//Table C: I want to end up like this </p>
  
  <pre><code>  Customer ID     Customer_Bill_Name       Item ID 1            Item ID 2           
      XXX001         XXX Company Name         WH15 02             600278 
      XX0002         YYY Company Name         600000  
  </code></pre>
  
  <p>Here is the SQL code i used to merge records using <code>JOIN</code></p>
  
  <pre><code>       $result = mysqli_query($con,"SELECT Customers.CustomerId,   
                Customers.Customer_Bill_Name, 
                Customers.WhichShipVia, 
                JrnlHdr.INV_POSOOrderNumber, 
                JrnlHdr.ShipByDate, 
                JrnlHdr.GoodThruDate, 
                JrnlHdr.CustomerSONo, 
                JrnlHdr.Reference, 
                JrnlHdr.TransactionDate, 
                JrnlRow.DistNumber, 
                JrnlRow.Quantity, 
                LineItem.ItemID, 
                LineItem.SalesDescription, 
                LineItem.PartNumber,
                LineItem.ItemDescription 
      FROM   Customers
      INNER JOIN JrnlHdr
      ON Customers.CustomerRecordNumber = JrnlHdr.CustVendId
      LEFT OUTER JOIN Address
      ON Customers.CustomerRecordNumber = Address.CustomerRecordNumber
      INNER JOIN JrnlRow // store sales order information
      ON JrnlHdr.PostOrder = JrnlRow.PostOrder
      LEFT OUTER JOIN BOMItems  // stores Bill of Materials attached to the sales order
      ON JrnlRow.ItemRecordNumber = BOMItems.AssemblyRecordNo
      INNER JOIN LineItem //Stores all Inventory Items
      ON JrnlRow.ItemRecordNumber = LineItem.ItemRecordNumber
  
     WHERE  JrnlHdr.JrnlKey_Journal = 11 
            AND JrnlHdr.PostOrder = JrnlRow.PostOrder 
            AND JrnlHdr.CustVendId = Customers.CustomerRecordNumber 
            AND JrnlRow.ItemRecordNumber = LineItem.ItemRecordNumber 
            AND JrnlHdr.POSOisClosed = 0 
            AND JrnlHdr.TransactionDate = '2014-11-17' 
    ORDER  BY JrnlHdr.Reference,JrnlRow.DistNumber");
  </code></pre>