MySQL Query: Optimal Querying

Question

i have this code

SELECT DISTINCT idx_campus_bookinfo,c.userid as Buyer,bookname,book_explain,writedate
FROM campus_bookinfo cb 
LEFT JOIN user_books ub ON idx_campus_bookinfo = id_product
LEFT JOIN customer c ON ub.id_customer = c.id_customer
where cb.idx_campus = 1 and cb.idxuser = 29 ORDER BY writedate DESC

which give an output of

Click to View output

My Question is how can i make the buyer column to be delimited by comma which has the same book.

Answer 1

Use the GROUP_CONCAT function, but it means having to replace DISTINCT with a GROUP BY:

    SELECT idx_campus_bookinfo,
           GROUP_CONCAT(c.userid SEPARATOR ',') as Buyer,
           bookname,
           book_explain,
           writedate
      FROM campus_bookinfo cb 
LEFT JOIN user_books ub ON idx_campus_bookinfo = id_product
LEFT JOIN customer c ON ub.id_customer = c.id_customer
    WHERE cb.idx_campus = 1 
      AND cb.idxuser = 29 
 GROUP BY idx_campus_bookinfo, bookname, book_explain, writedate
 ORDER BY writedate DESC

a followup question, can i sort the userid?

Yes, you can:

    SELECT idx_campus_bookinfo,
           GROUP_CONCAT(c.userid ORDER BY c.userid ASC SEPARATOR ',') as Buyer,
           bookname,
           book_explain,
           writedate
      FROM campus_bookinfo cb 
LEFT JOIN user_books ub ON idx_campus_bookinfo = id_product
LEFT JOIN customer c ON ub.id_customer = c.id_customer
    WHERE cb.idx_campus = 1 
      AND cb.idxuser = 29 
 GROUP BY idx_campus_bookinfo, bookname, book_explain, writedate
 ORDER BY writedate DESC

Answer 2

GROUP_CONCAT will take your user ids and concatenate them. Try this out.

SELECT idx_campus_bookinfo,
       GROUP_CONCAT(c.userid) as Buyer, bookname, book_explain, writedate
FROM campus_bookinfo cb 
LEFT JOIN user_books ub ON idx_campus_bookinfo = id_product
LEFT JOIN customer c ON ub.id_customer = c.id_customer
WHERE cb.idx_campus = 1 and cb.idxuser = 29
GROUP BY idx_campus_bookinfo
ORDER BY writedate DESC