CODEWITHSUNDEEP
mysqlsql
Shota
Shota

Reputation: 513

How to make a list between two table with all possible combination?

I have two tablee.

Table A
  ID
A001
A002
A003

Table B
 COUNTRY
      UK
     USA
 GERMANY
   CHINA

I'd like to list all posible combination from each ID and each CONTRY

My expected result should be:

A001        UK
A001       USA
A001   GERMANY
A001     CHINA
A002        UK
A002       USA
A002   GERMANY
A002     CHINA
A003        UK
A003       USA
A003   GERMANY
A003     CHINA

Upvotes: 0

Views: 45

Answers (3)

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14228

You can use Cross Join to resolve it, Read @Sarath Avanavu's answer in this link to have a better understanding.

SELECT a.ID, b.COUNTRY
FROM TABLE a
Cross Join TABLE b

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522396

You may use a CROSS JOIN here. Note for completeness that in MySQL, an inner join without any join criteria behaves like a cross join. So, we could actually try:

SELECT a.ID, b.COUNTRY
FROM TableA a
INNER JOIN TableB b
ORDER BY a.ID, b.COUNTRY;

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13016

this is just cross join.

select * from tableA t1
cross join tableB
order by t1.ID

Upvotes: 1

Related Questions