MySQL 5 - How to convert a table

Hi, all,

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

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 2*2*2=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?

----------------
Thanks & regards
Lokananth
Live chat By miOOt

So you are trying to find the cartesian product of the various options and then find out which one are missing?

This is a logical problem so the database would not be able to provide it for you easily. You could make a stored procedure to get the various individual values, create the cartesian product in a table and then OUTER JOIN the 2 tables to see what is missing.