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

Inner Join Help

Fuhrmann

Well-known member
#1
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

Well-known member
#2
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

Well-known member
#3
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

Well-known member
#4
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

Well-known member
#5
Code:
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.