How to convert a table

[gilbertsavier]

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?

[sanky12ka4]

i dont think there is such query.
you can fetch it sepratly,use loop to get desire result