Inner Join Help

[Fuhrmann]

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?

 

[steel_curtain]

Something like this?

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]

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.

 

[steel_curtain]

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]

SELECT pn.name_id,
      n.gender,
      GROUP_CONCAT(p.product_id SEPARATOR ',') AS prodcut_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

Amazing!! That works. Thank you so much.