CODEWITHSUNDEEP
mysql
user1286499
user1286499

Reputation: 227

How do I get full GROUP_CONCAT list on results with WHERE statement?

Here an example my database structure.. 

uid  email            name
-------------------------------
1    [email protected]   John
2    [email protected]  Peter
3    [email protected]  Colin

book table

book_id  book_name
-------------------
1        PHP
2        MySQL
3        Javascripts
4        CSS

history table

history_uid  history_bookid
---------------------------
1            1
1            2
1            3
2            1
2            3
3            3

Current statement including multi select query on history.history_bookid

SELECT users.uid,
       users.email,
       users.`name`,
       history.history_uid,
       history.history_bookid,
       GROUP_CONCAT(history.history_bookid) AS BookID,
       GROUP_CONCAT(book_name)              AS bookTitles
FROM   users
       INNER JOIN history
         ON users.uid = history.history_uid
       INNER JOIN book
         ON history.history_bookid = book.book_id
WHERE  history.history_bookid = 1
        OR history.history_bookid = 3
GROUP  BY users.uid

The results

uid email            name   history_uid  history_bookid  BookID   bookTitles
---------------------------------------------------------------------------------
1   [email protected]   John   1            3               3,1      Javascripts,PHP
2   [email protected]  Peter  2            1               1,3      PHP,Javascripts
3   [email protected]  Colin  3            3               3        Javascripts

Question :

How do I get full list of books was read by uid = 1? Example output I want is including all book in history table

uid email            name   history_uid  history_bookid  BookID   bookTitles
---------------------------------------------------------------------------------
1   [email protected]   John   1            3               1,2,3    PHP,MySQL,Javascripts
2   [email protected]  Peter  2            1               1,3      PHP,Javascripts
3   [email protected]  Colin  3            3               3        Javascripts

Upvotes: 0

Views: 151

Answers (2)

eggyal
eggyal

Reputation: 125925

How do I get full list of books was read by uid = 1?

Add that as a filter option to your WHERE clause:

WHERE history.history_bookid IN (1,3) OR users.uid = 1

Upvotes: 1

Marc B
Marc B

Reputation: 360762

You mean you want the titles as well as the book ids?

Add a GROUP_CONCAT(book_name) AS bookTitles to your field list.

Upvotes: 1

Related Questions