Results 1 to 4 of 4

Thread: Sql query

  1. #1
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33

    Unanswered: Sql query

    Hello All,

    I have the following Structure and Data sample.
    I have to now create a Materialized view.
    Can anybody please help me.

    Table A
    TBLA_ID Num 10
    TBLA_RID Num 10
    TBLA_Name var 50
    TBLA_Value Var 40


    Table B
    TBLB_ID Num 10
    TBLB_NAMe var 40


    Table C
    TBLC_ID Num 10
    TBLC_NAMe var 40

    Table D
    TBLD_ID Num 10
    TBLD_NAMe var 40


    Datas:
    Table A
    -------
    TBLA_ID TBLA_RID TBLA_Name TBLA_Value
    1 1 Test1 QA
    2 1 TBLB_ID 2
    3 1 TBLC_ID 3
    4 3 Test2 Test
    5 3 TBLD_ID 5
    6 3 Desc Sample Data
    7 3 OrdNum 34567
    8 3 Repo Test

    Table B
    TBLB_ID TBLB_NAMe
    1 Sale
    2 Name1
    3 Name2
    4 Name3
    5 Name4

    Table C
    TBLC_ID TBLB_NAMe
    1 TBLCNAmeX
    2 TBLCName1
    3 TBLCName2
    4 TBLCName3
    5 TBLCName4
    6 TBLCNameY

    Table D
    TBLD_ID TBLB_NAMe
    1 TBLDNAmeX
    2 TBLDName1
    3 TBLDName2
    4 TBLDName3
    5 TBLDName4
    6 TBLDNameY


    Table A has the field TBLA_RID which has reference to another master table.
    for each TBLA_RID row there could be a TBLB_ID or TBLC_ID or TBLD_ID value
    stored.

    Now my query should map these values to the Table B,Table C or Table C tables.
    So the query should check Table A.TBLA_Name, and map to the B,C and D tables
    based on the Id value stored in the field TBLA_Value.

    Thanx.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Use a JOIN.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33
    Hi,,

    Sorry I made a mistake with the Field name.

    Old Text:
    Table A has the field TBLA_RID which has reference to another master table.
    for each TBLA_RID row there could be a TBLB_ID or TBLC_ID or TBLD_ID value
    stored.

    Actually it should be:
    Field TBLA_Name will store the field to refer in which table so it could be
    TBLB_ID or TBLC_ID or TBLD_ID. Now based on the field name stored in this field TBLA_Name, I have to related to either Table A, Table B, Table C.

    thanx.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    That is TERRIBLE AND COMPLICATED design!
    Why make life so hard on yourself??
    There are about 20 better ways of doing this.
    How about you describe how/why you need to design this way and we
    can come up with something more suitable?

    My question is:
    How come you don't know what type of data you need values for?
    Why not combine tables B, C, D into one table?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •