One to many in mysql select

Databases
1

Hello Team ,

So i have this little problem and i don’t want to solve it using php …

I have 3 tables :

  • board (kind of ads)
  • generic_list (contains lists of tags)
  • board_relations (it contains tags/associated with the board)

the board relations table contains the id of the board , the id of the generic list and the value .

is there a way for a query to give me the following :

Info of the board table , then inside another field a list of the values of a specific generic_list .

i currently have the following query :

SELECT
jos_hpj_users_boards.id,
jos_hpj_users_boards.userId,
jos_hpj_users_boards.title,
jos_hpj_users_boards.description,
jos_hpj_users_boards.status
FROM
jos_hpj_users_boards
Inner Join jos_hpj_users_boards_relations ON jos_hpj_users_boards.id = jos_hpj_users_boards_relations.boardId
Inner Join hpj_genericlist ON jos_hpj_users_boards_relations.listId = hpj_genericlist.idList
GROUP BY
jos_hpj_users_boards.id

which returns something like :

I want something like :

here is my generic list :

and finally my relation board :

is there a way to do that in Mysql ?

2

did i explain correctly my question ?

3

i had a little trouble understanding “list of the values of a specific generic_list”

try this –

SELECT jos_hpj_users_boards.id
     , jos_hpj_users_boards.userId
     , jos_hpj_users_boards.title
     , jos_hpj_users_boards.description
     , jos_hpj_users_boards.status
     , [COLOR="blue"]GROUP_CONCAT(jos_hpj_users_boards_relations.value) AS board_values[/COLOR]
  FROM jos_hpj_users_boards
INNER 
  JOIN jos_hpj_users_boards_relations 
    ON jos_hpj_users_boards_relations.boardId = jos_hpj_users_boards.id
INNER 
  JOIN hpj_genericlist 
    ON hpj_genericlist.idList = jos_hpj_users_boards_relations.listId
   [COLOR="Blue"]AND jos_hpj_users_boards_relations.listId = 'DIPLOME_LIST'[/COLOR]
GROUP 
    BY jos_hpj_users_boards.id