Cross Join is creating a Loop

Question

I have three tables, I need to make a CROSS JOIN between them to display the IDs and names of the products and the stores that sell them, here is an example of how now

TABLE_PRODUCT

ID_PRODUCT  |   NAME_PRODUCT  
1           |   Addidas Super Star
2           |   Calvin Klein BAG

TABLE_STORE

ID_STORE |   NAME_STORE 
1        |   ThE 98
2        |   C&A

TABLE_PROD_STOR

ID_STORE  |  ID_PROD
1         |  1
2         |  2

And I need the result in the format:

ID STORE  |  STORE  | ID PRODUCT |       PRODUCT      |
1         |  ThE 98 | 1          | Addidas Super Star |
2         |  C&A    | 2          | CALVIN KLEIN HAT   |

My biggest problem is that when I do the CROSS JOIN, the names are repeated twice each. What is the solution?

Answer 1

Select
y.ID_STORE  ,
y.NAME_STORE store ,
x.ID_PRODUCT,
x.NAME_PRODUCT  product
from
TABLE_PRODUCT x inner join
TABLE_STORE y 
   on x.ID_PRODUCT =y.ID_STORE 
inner join TABLE_PROD_STOR z
   on x.ID_PRODUCT =z.ID_STORE