Wilhelm Bernschaf Wilhelm Bernschaf - 1 month ago 5
SQL Question

Cross Join is creating a Loop

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
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