CODEWITHSUNDEEP
mysqlgroupingrows
wirestealth
wirestealth

Reputation: 11

MySQL returning multiple row values in a single column

I have this query:

SELECT 
    s.id AS id, 
    s.item_id, 
    i.type_id AS type_id, 
    i.description AS description, 
    i.owner_id, 
    s.buyer_id, 
    i.receivedate, 
    i.statusdate, 
    i.baseprice, 
    i.salepercentage, 
    i.status_id, 
    s.posteddate, 
    s.closingdate, 
    s.discountdate, 
    s.discountrate, 
    t.description AS type_description, 
    st.description AS status_description
FROM 
    sale s
LEFT JOIN item i ON s.item_id=i.id 
LEFT JOIN TYPE t ON i.type_id=t.id 
LEFT JOIN STATUS st ON i.status_id=st.id 
WHERE s.flag_id != 2
GROUP BY s.id
ORDER BY s.closingdate;

It works fine, but as a final step I need to pull in the concatenated firstname and lastname fields from yet another table called customer for both the i.owner_id and s.buyer_id fields.

I've got close using the following:

SELECT 
    s.id AS id, 
    s.item_id, 
    i.type_id AS type_id, 
    i.description AS description, 
    i.owner_id, 
    s.buyer_id, 
    i.receivedate, 
    i.statusdate, 
    i.baseprice, 
    i.salepercentage, 
    i.status_id, 
    s.posteddate, 
    s.closingdate, 
    s.discountdate, 
    s.discountrate, 
    t.description AS type_description, 
    st.description AS status_description, 
    GROUP_CONCAT(CONCAT(c.firstname, ' ', c.lastname) ORDER BY i.owner_id, s.buyer_id)
FROM 
    sale s 
LEFT JOIN item i ON s.item_id=i.id 
LEFT JOIN TYPE t ON i.type_id=t.id 
LEFT JOIN STATUS st ON i.status_id=st.id 
LEFT JOIN CUSTOMER c ON i.owner_id=c.id OR s.buyer_id=c.id
WHERE s.flag_id != 2
GROUP BY s.id
ORDER BY s.closingdate;

When I test it using entries in the customer table like the following:

id=3, firstname="John", lastname="Doe"
id=10, firstname="Jane", lastname="Do"

And the items from the sale table have:

owner_id=3, buyer_id=10
owner_id=10, buyer_id=3

The GROUP_CONCAT() column returns

Jane Do,John Doe
Jane Do,John Doe

I have no way of knowing which is the owner and which is the buyer purely off the comma separated position as the results I was hoping for were

John Doe,Jane Do
Jane Do,John Doe

for the sale items.

Is it possible to ensure that the result set returned will have the GROUP_CONCAT() list returned in a controlled order (i.e. the first customer will be associated with the owner_id and the 2nd customer with the buyer_id)?

Upvotes: 1

Views: 639

Answers (2)

Marc B
Marc B

Reputation: 360702

I'd suggest joining the customer table twice. once for buyer, once for owner:

SELECT ...
   GROUP_CONCAT(CONCAT(buyer.firstname, ' ', buyer.lastname) AS buyers,
   GROUP_CONCAT(CONCAT(owner.firstname, ' ', owner.lastname) AS owners
...
LEFT JOIN customer AS buyer ON s.buyer_id=buyer.id
LEFT JOIN customer AS owner ON i.owner_id=owner.id

If you need data to be kept separate, then keep it separate.

Upvotes: 3

Johan
Johan

Reputation: 76567

The problem is that you are mixing owners and buyers.
Just join them separately;
it's no problem joining on the same table twice as long as you use a different alias for each instance. 

SELECT 
  s.id AS id, s.item_id
  , i.type_id AS type_id, i.description AS description, i.owner_id
  , s.buyer_id
  , i.receivedate, i.statusdate, i.baseprice, i.salepercentage, i.status_id
  , s.posteddate, s.closingdate, s.discountdate, s.discountrate
  , t.description AS type_description
  , st.description AS status_description
  , GROUP_CONCAT(
      CONCAT(co.firstname, ' ', co.lastname,',',cb.firstname,' ',cb.lastname) 
      ORDER BY co.id,cb.id) as first_owner_than_buyer
FROM sale s 
LEFT JOIN item i ON s.item_id=i.id 
LEFT JOIN TYPE t ON i.type_id=t.id 
LEFT JOIN STATUS st ON i.status_id=st.id 
LEFT JOIN CUSTOMER co ON (i.owner_id=co.id)
LEFT JOIN CUSTOMER cb ON (s.buyer_id=cb.id)
WHERE s.flag_id != 2
GROUP BY s.id
ORDER BY s.closingdate;

Upvotes: 1

Related Questions