mysql - 使用 JOIN 合并和联合来自两个表的记录

Question

我如何使用 JOIN 合并和 UNION 两个表中的记录。例如，假设我有下面的表 A 和 B，并希望像表 C 一样结束。

我想提取并合并所有与 tableA.Customer ID 匹配的 tableB.Item ID，如果有多个匹配项，那么我想创建一个新字段哪个持有另一个。

//表A

  Customer ID     Customer_Bill_Name.......                  
    XXX001           XXX Company Name......           
    XXX002           YYY Company Name......         

//表B

 Customer ID          Item ID 1           
     XXX001            WH15 02
     XXX001            600278        
     XX0002            600000      

//表C:我想这样结束

  Customer ID     Customer_Bill_Name       Item ID 1            Item ID 2           
    XXX001         XXX Company Name         WH15 02             600278 
    XX0002         YYY Company Name         600000  

这是我使用 JOIN

合并记录的 SQL 代码

       $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");

Answer 1

也许你可以使用group_concat , 这会将所有值连接到一列。

select a.*, group_concat(b.ItemId) joined
from tableA a inner join tableB b on a.customerId = b.customerId
group by a.customerId, a.customer, a.company

结果:

CUSTOMERID  CUSTOMER    COMPANY         JOINED
XXX001      XXX         Company Name    600278,WH15 02
XXX002      YYY         Company Name    600000

SQL Fiddle example