SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question One to many in mysql select

    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 :

    Code MySQL:
    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 ?
    Attached Images Attached Images
    Thai Boxing
    E-Pix
    Chaaban
    Tekfirm Want to have a web site ?

  2. #2
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    did i explain correctly my question ?
    Thai Boxing
    E-Pix
    Chaaban
    Tekfirm Want to have a web site ?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by commandos View Post
    did i explain correctly my question ?
    i had a little trouble understanding "list of the values of a specific generic_list"

    try this --
    Code:
    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
         , GROUP_CONCAT(jos_hpj_users_boards_relations.value) AS board_values
      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
       AND jos_hpj_users_boards_relations.listId = 'DIPLOME_LIST'
    GROUP 
        BY jos_hpj_users_boards.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •