#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2007
    Posts
    38
    Rep Power
    7

    Firebird query make row as column on 2 table


    hi,pls help me, i will try to explain what i want to achieve

    table A
    id namelocation
    0 warehouse 1
    1 warehouse 2

    table B
    id nameproduct qty location
    0 keyboard 2 warehouse 1
    1 Mouse 5 warehouse 2


    query result
    nameproduct warehouse 1 warehouse 2
    keyboard 2 0
    mouse 0 5

    how can i query that? the row on table a is not limited only two,so i try to dynamically generate column base on table a row


    thx
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    55
    Rep Power
    6
    Let's go! (if I understand)

    Table "A" -> have the location
    Table "B" -> have the products

    SO, we need one Table "C" for store the UNION of two table!

    Table "C" fields:
    1 - ID -> autoincrement for use future and NOT DUPLICATE new entry (basic task in all tables, except, in some case where should be some ONE item in relationship = ID will be the ID of table main)
    2 - ID_LOCATION -> for bind to Table "A" (field ID)
    3 - ID_PRODUCTS -> for bind to Table "B" (field ID)

    So, we have a N -> to -> N.

    --------------------
    ID - ID_LOCATION - ID_PRODUCTS
    1 1 1
    2 1 2
    3 2 1
    4 2 2

    For NOT DUPLICATION record, you can to use one UNIQUE INDEX for fields (ID_LOCATION, ID_PRODUCTS)

    See "how add CONSTRAINT FOREIGN KEY"

    ALTER TABLE DES
    ADD CONSTRAINT FK_DES_1 FOREIGN KEY (ENV_NAME) REFERENCES DISTROS (DE);

    http://www.firebirdsql.org/refdocs/langrefupd20-create-table.html

    http://www.firebirdsql.org/refdocs/langrefupd20-alter-table.html

    Join the Tables Results

    http://stackoverflow.com/questions/2797286/how-to-have-multiple-tables-with-multiple-joins
    Last edited by emailx45; July 9th, 2013 at 12:00 PM.

IMN logo majestic logo threadwatch logo seochat tools logo