How to convert a table

MySQL & MariaDB Other MySQL® Questions
1

Hi, all,

I have a problem to convert table content based on existing content:

table: list:

DROP TABLE IF EXISTS list;CREATE TABLE list ( FACILITY varchar(30) NOT NULL, PRODUCT varchar(30) NOT NULL, SEGMENT varchar(30) NOT NULL, PRIMARY KEY (FACILITY,PRODUCT,SEGMENT)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Let’s say I know,
I have 2 FACILITY: AUSTIN, BOSTON;
(by
select distinct facility from list;
)
I have 2 PRODUCT: A, B;
(by
select distinct product from list;
)
I have 2 SEGMENT: BIG, SMALL;
(by
select distinct segment from list;
)

the Total combination could be 222=8

Now the current table “list” contains below data (3 records):
AUSTIN, A, BIG;
AUSTIN, B, SMALL;
BOSTON, A, SMALL;

How can I tell the other 5 combination by MYSQL?

2

I’m not sure this is what you want, but it will give you the full set of permutations, even if there are no rows that contain those exact permutations:

SELECT t1.FACILITY, t2.PRODUCT, t3.SEGMENTFROM (SELECT DISTINCT FACILITY AS FACILITY FROM list) AS t1, (SELECT DISTINCT PRODUCT AS PRODUCT FROM list) AS t2, (SELECT DISTINCT SEGMENT AS SEGMENT FROM list) AS t3

3

Yes, this is what i want to have a set of combination.

the full SQL code likes this:

SELECT a.* FROM(SELECT t1.FACILITY, t2.PRODUCT, t3.SEGMENTFROM (SELECT DISTINCT FACILITY AS FACILITY FROM list) AS t1, (SELECT DISTINCT PRODUCT AS PRODUCT FROM list) AS t2, (SELECT DISTINCT SEGMENT AS SEGMENT FROM list) AS t3) aLEFT JOIN list bUSING (FACILITY,PRODUCT,SEGMENT)WHERE b.FACILITY IS NULLORDER BY a.FACILITY,a.PRODUCT,a.SEGMENT