Announcement

Announcement Module
Collapse
No announcement yet.

How to convert a table

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to convert a table

    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 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?

  • #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

    Comment


    • #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

      Comment

      Working...
      X