CODEWITHSUNDEEP
mysqljoin
Patricks
Patricks

Reputation: 769

Join tables to concat every possible combination

I have two mysql tables. One with article numbers and one with variants numbers. No I want to join the tables so have a result table with every possible article/variant combination. For example:

Article numbers table:

+-----------+-------------+
| ArticleNo | ArticleName |
+-----------+-------------+
|      0001 | Product 1   |
|      0002 | Product 2   |
|      0003 | Product 3   |
+-----------+-------------+

Variants numbers table:

+-----------+-------------+
| VariantNo | VariantName |
+-----------+-------------+
|      1001 | Variant 1   |
|      1002 | Variant 2   |
|      1003 | Variant 3   |
+-----------+-------------+

Result table:

+----------+---------------------+
| ResultNo |     ResultName      |
+----------+---------------------+
| 00011001 | Product 1 Variant 1 |
| 00011002 | Product 1 Variant 2 |
| 00011003 | Product 1 Variant 3 |
| 00021001 | Product 2 Variant 1 |
| 00021002 | Product 2 Variant 2 |
| 00021003 | Product 2 Variant 3 |
| 00031001 | Product 3 Variant 1 |
| 00031002 | Product 3 Variant 2 |
| 00031003 | Product 3 Variant 3 |
+----------+---------------------+

Upvotes: 1

Views: 114

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You are looking for a Cartesian product. In SQL this is provided through CROSS JOIN:

select concat(a.ArticleNo, v.VariantNo) as ResultNo,
       concat_ws(' ', a.ArticleName, v.VariantName) as ResultName
from articles a cross join
     variants v
order by a.ArticleName, v.VariantName;

Upvotes: 2

SF..MJ
SF..MJ

Reputation: 880

You can write Directly:

SELECT  CONCAT(a.ArticleNo,v.VariantNo) as ResultNo, 
CONCAT(a.ArticleName,v.VariantName) as ResultName 
FROM Article a,Variants v

It will produce Cross Join

Upvotes: 2

Hubert Sadecki
Hubert Sadecki

Reputation: 333

I think this is what you are looking for. This kind of operation generates Cartesian Product. To solve your problem you should apply this query from link above and it should look like this:

select concat(ArticleNo,VariantNo ) as ResultNo ,
concat(ArticleName,VariantName) as  ResultName 
from Article 
cross join Variants

Upvotes: -1

Related Questions