1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Inner Join Help

Discussion in 'XenForo Development Discussions' started by Fuhrmann, Dec 17, 2011.

  1. Fuhrmann

    Fuhrmann Well-Known Member

    I have three databases:

    - Names
    name_id || gender
    1 || Fuhrmann

    - Product
    product_id || product_name
    5 || Book
    6 || Phone

    - ProductNames
    name_id || product_id
    1 || 5
    1 || 6


    What I'm trying to do it is get all the information (product_name, product_id, name_id, gender) in one query.

    I already have wrote sql query that uses a inner join, but in the case of the ProductNames table, we have two diferentts fields. This is when it get difficult.

    Any help?
     
  2. steel_curtain

    steel_curtain Well-Known Member

    Something like this?

    Code:
    SELECT p.product_name, p.product_id, pn.name_id, n.gender
    FROM (
     
    Names AS n
    INNER JOIN ProductNames AS pn ON pn.name_id = n.name_id
    )
    INNER JOIN Product AS p ON pn.product_id = p.product_id
     
    Fuhrmann likes this.
  3. Fuhrmann

    Fuhrmann Well-Known Member

    The problem is that one user can have assigned to it multiples values. I just want to get the user with the name_id = 1.

    In XenForo, I can use the fetchAll function, but this return me two completes rows. One if the product_id = 5 and the other row will give me all the same results from the others collums, but with the product_id = 6.

    Is there a way to have the multiple values of the table ProductNames in only one row?


    I have tried to use the fetchRow, but then this return to me only one row (sure..) with only the product_id = 5.
     
  4. steel_curtain

    steel_curtain Well-Known Member

    Code:
    SELECT pn.name_id,
          n.gender,
          GROUP_CONCAT(p.product_id SEPARATOR ',') AS product_ids,
          GROUP_CONCAT(p.product_name SEPARATOR ',') AS products
    FROM (
     
    Names AS n
    INNER JOIN ProductNames AS pn ON pn.name_id = n.name_id
    )
    INNER JOIN Product AS p ON pn.product_id = p.product_id
    GROUP BY pn.name_id
    
     
    Fuhrmann likes this.
  5. Fuhrmann

    Fuhrmann Well-Known Member

    Amazing!! That works. Thank you so much.
     

Share This Page