MYSQL GROUP_CONCAT and half dosen JOINs

Question

I have been trying to get this query to work but I am stumbed right now. Problem is on the JOIN with GROUP_CONCAT in it. I just can't figure how to get it working.

What I want is to get buntch of data from RFQs and joining Customers, etc. to it and everything was fine until I had to change Suppliers field so that it could have multiple suplliers per one RFQs row. I created new tables RFQsSuppliers where I combine Supplier.ID's and RFQs.ID's and I have table Suppliers that contains the names and other stuff.

I want to get Suppliers to have all suppliers separated with ','.

My Query:

$result = mysql_query("SELECT     Pullero.DateAdded as DateAdded,
                              Customers.Name as customer,
                              Pullero.ID as RFQID,
                              Ships.Name as ship,
                              Pullero.CustomerRef as CustomerRef,
                              Contacts.FirstName as contactF,
                              Contacts.LastName as contactL,
                              Contacts.Email as contactE,
                              Users.tunnus as handler,
                              RFQStatus.Name as status,
                              Pullero.Description as RFQDescription,
                              Pullero.LastEdited as LastEdit
                   FROM       RFQs Pullero
                   JOIN      (SELECT    RFQs.ID,
                                        GROUP_CONCAT(Supplier.Name) AS Suppliers
                              FROM      RFQs 
                              LEFT JOIN RFQsSuppliers ON RFQs.ID = RFQsSuppliers.RFQID
                              JOIN      Suppliers ON RFQsSuppliers.SupplierID = Suppliers.ID
                              GROUP BY  RFQs.ID) 
                              RFQsSuppliers ON Pullero.ID = RFQsSuppliers.RFQID

                   LEFT JOIN  Ships ON RFQ.ShipID=Ships.ID
                   LEFT JOIN  Contacts ON RFQ.ContactID=Contacts.ID
                   LEFT JOIN  Customers ON RFQ.CustomerID=Customers.idCustomers
                   LEFT JOIN  Users ON RFQ.PriJobHandler=Users.id
                   LEFT JOIN  RFQStatus ON RFQ.StatusID=RFQStatus.ID
                   WHERE      RFQs.LastEdited > '$lastedited'
                   ORDER BY   RFQs.LastEdited ASC
                  ") or die(mysql_error());

At the moment, error is :Unknown column 'Supplier.Name' in 'field list'

EDIT

Below is some expample of my Table desing:

  RFQs
  ID | DateAdded | CustomerID | ShipID | LastEdited | StatusID ...

  /* -------------------------------------- */
  Suppliers
  ID | Name | CountryID
  1    Sup1   2
  2    Sup2   5
  3    Sup3   3
  4    Sup4   3
  /* -------------------------------------- */
  RFQsSuppliers
  ID | RFQID | SupplierID
  1    1       4
  2    2       3
  3    56      3
  4    4       3
  5    39      1
  6    56      1
  7    4       4

I tried to get only the suppliers with following query:

$result = mysql_query("SELECT     Suppliers.Name as Suppliers
                   FROM       RFQs
                   LEFT JOIN  RFQsSuppliers ON RFQs.ID=RFQsSuppliers.SupplierID
                   LEFT JOIN  Suppliers ON RFQsSuppliers.SupplierID=Suppliers.ID
                   GROUP BY   RFQs.ID
                  ") or die(mysql_error());

But print_r on each row returns only following:

Array ( [Suppliers] => Sup1,Sup1 ) Array ( [Suppliers] => ) Array ( [Suppliers] => Sup4,Sup4 ) Array ( [Suppliers] => ) Array ( [Suppliers] => ) Array ( [Suppliers] => )

Any ideas?

Simon at The Access Group · Accepted Answer · 2012-02-18T21:57:40.320

JOIN      (
    SELECT    RFQs.ID,
              GROUP_CONCAT(Supplier**s**.Name) AS Suppliers
    FROM      RFQs 
    LEFT JOIN RFQsSuppliers ON RFQs.ID = RFQsSuppliers.RFQID
    JOIN      Suppliers ON RFQsSuppliers.SupplierID = Suppliers.ID
    GROUP BY  RFQs.ID
) RFQsSuppliers

Try that

I believe you missed an 's' in your GROUP_CONCAT table name as you had it as Supplier.Name instead of Suppliers.name as per the table

Edit

Additionally you are referring to RFQs.LastEdited in WHERE and ORDER BY clauses however you aliased table RFQs to be named Pullero, so those will need changing to Pullero.LastEdited

Edited edit Reformatted to use JOINs instead of SUBQUERY

SELECT     
    Pullero.DateAdded as DateAdded,
    Customers.Name as customer,
    Pullero.ID as RFQID,
    GROUP_CONCAT(Suppliers.Name) AS Suppliers,
    Ships.Name as ship,
    Pullero.CustomerRef as CustomerRef,
    Contacts.FirstName as contactF,
    Contacts.LastName as contactL,
    Contacts.Email as contactE,
    Users.tunnus as handler,
    RFQStatus.Name as status,
    Pullero.Description as RFQDescription,
    Pullero.LastEdited as LastEdit

FROM       RFQs AS Pullero
LEFT JOIN  RFQsSuppliers ON RFQsSuppliers.RFQID = Pullero.ID
LEFT JOIN  Suppliers ON RFQSuppliers.SupplierID = Suppliers.ID
LEFT JOIN  Ships ON RFQ.ShipID=Ships.ID
LEFT JOIN  Contacts ON RFQ.ContactID=Contacts.ID
LEFT JOIN  Customers ON RFQ.CustomerID=Customers.idCustomers
LEFT JOIN  Users ON RFQ.PriJobHandler=Users.id
LEFT JOIN  RFQStatus ON RFQ.StatusID=RFQStatus.ID
WHERE      Pullero.LastEdited > '$lastedited'
ORDER BY   Pullero.LastEdited ASC

Or for an example using original subquery, the RFQs link seems superfulous so I've adjusted it + have added the Suppliers list into the output

$result = mysql_query("SELECT     Pullero.DateAdded as DateAdded,
                              RFQsSuppliers.Suppliers,
                              Customers.Name as customer,
                              Pullero.ID as RFQID,
                              Ships.Name as ship,
                              Pullero.CustomerRef as CustomerRef,
                              Contacts.FirstName as contactF,
                              Contacts.LastName as contactL,
                              Contacts.Email as contactE,
                              Users.tunnus as handler,
                              RFQStatus.Name as status,
                              Pullero.Description as RFQDescription,
                              Pullero.LastEdited as LastEdit
                   FROM       RFQs Pullero
                   JOIN      (
                      SELECT    RFQsSuppliers.RFQID,
                                GROUP_CONCAT(Suppliers.Name) AS Suppliers
                      FROM      RFQsSuppliers.RFQID
                      JOIN      Suppliers ON RFQsSuppliers.SupplierID = Suppliers.ID
                      GROUP BY  RFQsSuppliers.RFQID
                    ) AS RFQsSuppliers ON Pullero.ID = RFQsSuppliers.RFQID
                   LEFT JOIN  Ships ON RFQ.ShipID=Ships.ID
                   LEFT JOIN  Contacts ON RFQ.ContactID=Contacts.ID
                   LEFT JOIN  Customers ON RFQ.CustomerID=Customers.idCustomers
                   LEFT JOIN  Users ON RFQ.PriJobHandler=Users.id
                   LEFT JOIN  RFQStatus ON RFQ.StatusID=RFQStatus.ID
                   WHERE      Pullero.LastEdited > '$lastedited'
                   ORDER BY   Pullero.LastEdited ASC
                  ") or die(mysql_error());

EDIT Updated query for second part of question

$result = mysql_query("SELECT RFQs.ID, GROUP_CONCAT(Suppliers.Name) as Suppliers
                   FROM       RFQs
                   LEFT JOIN  RFQsSuppliers ON RFQs.ID=RFQsSuppliers.RFQID
                   LEFT JOIN  Suppliers ON RFQsSuppliers.SupplierID=Suppliers.ID
                   GROUP BY   RFQs.ID
                  ") or die(mysql_error());

Your right. I can't believe why I missed that while error message told me that.. I corrected it.. but now it says: Unknown column 'RFQs.LastEdited' in 'where clause' and that IS be correct. — Santtu, Feb 17 '12 at 13:51
OK, Changed all the required fields to Pullero as they should have been in the firts place .... Now it returns the same row 5 times and Supplier field is null.. — Santtu, Feb 17 '12 at 14:01
In the join you are joining RFQs.id to RFQsSuppliers.SupplierID. I believe you intend to join to RFQsSuppliers.RFQID. Will post up a suggested alternative as there are other changes possible — Simon at The Access Group, Feb 18 '12 at 21:55

Michael Berkowski · Answer 2 · 2012-02-17T13:56:00.067

0

You're referencing the column Supplier.Name, but the table join is on a table called Suppliers, plural.

      (SELECT    RFQs.ID,
          /* Should be plural Suppliers.Name here... */
          GROUP_CONCAT(Suppliers.Name) AS Suppliers
       FROM      RFQs 
       LEFT JOIN RFQsSuppliers ON RFQs.ID = RFQsSuppliers.RFQID
       JOIN      Suppliers ON RFQsSuppliers.SupplierID = Suppliers.ID
       GROUP BY  RFQs.ID)

Later, in your final WHERE clause, you reference RFQs, however you have aliased that table as Pullero:

FROM       RFQs Pullero

Change the WHERE clause to use

WHERE      Pullero.LastEdited > '$lastedited'
           ORDER BY   Pullero.LastEdited ASC

edited Feb 17 '12 at 13:56

answered Feb 17 '12 at 13:43

Michael Berkowski

267,341
46
444
390

@Santtu See addition above. You aliased `RFQs` to `Pullero`. – Michael Berkowski Feb 17 '12 at 13:56
OK, Changed all the required fields to Pullero as they should have been in the firts place .... Now it returns the same row 5 times and Supplier field is null. Insted of returning it one time with one supplier – Santtu Feb 17 '12 at 14:05

MYSQL GROUP_CONCAT and half dosen JOINs

EDIT

2 Answers2