Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Lightbulb Unanswered: How to change a 1:M relation to a 1:1 view

    I have a problem at my work place and need to find if there is an answer for this exact problem :

    Table A
    ID
    Name
    Adress
    Etc...

    Data of Table A :
    ID Name Adress
    1 Bob Montreal
    2 Jim Laval

    Table B
    ID
    FlagName
    FlagValue

    Data of Table B :
    ID FlagName FlagValue
    1 Married Yes
    2 Marries No

    I need to have an SQL query that will take B's records as columns. This is an example of what I wish to have :

    ID Name Adress Maried
    1 Bob Montreal Yes
    2 Jim Laval No

    Does anyone have an answer? We're stuck with an existing database poorly designed and don't have means to change them. We wish to create a view to accomodate a few changes.

    Frank (brouminator@hotmail.com)

  2. #2
    Join Date
    Aug 2003
    Posts
    123
    Hi,

    I do not know whether I understood your problem correctly, you can create a view as follows.

    create view <view_name> is select a.id,a.name,a.adress,b.flag from
    tableA a, tableB b
    where a.id=b.id

    If this is not solving your problem, give me more detail I will help you.


    --Jaggu

  3. #3
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: How to change a 1:M relation to a 1:1 view

    Originally posted by brouminator
    I have a problem at my work place and need to find if there is an answer for this exact problem :
    I need to have an SQL query that will take B's records as columns. This is an example of what I wish to have :

    ID Name Adress Maried
    1 Bob Montreal Yes
    2 Jim Laval No

    If you create a view of Table B as

    CREATE OR REPLACE VIEW MARRIAGE_STAT_VW AS
    SELECT ID, FLAGVALUE AS MARRIED
    FROM TABLE B
    WHERE FLAGNAME = 'MARRIED'
    ORDER BY ID

    Then you can do your select as
    SELECT ID, Name, Address, Married
    FROM TABLEA, TABLEB
    WHERE TABLEA.ID=TABLEB.ID(+)
    ORDER BY ID


    If there is any other data in Table B that you need to return, then you will need to create additional views.

    Some other suggestions depending on vendor support......
    1. If the vendor is still loking at and modifying your database, make sure that you indicate that it is your companies doing such as putting in "CUST" in the views name.
    2. Indicate that it is a view by pre/suffixing with "VW" or "VIEW" or something similar.
    3. Write down/document somewhere any changes you are making to the database.
    Just some thoughts.
    Last edited by jimpen; 09-02-03 at 15:44.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  4. #4
    Join Date
    Sep 2003
    Posts
    4
    Unfortunately, you didn't. Creating a view is not a problem, it's finding the right query for the view to have, that is bugging me.

    I'm not looking for a query with a.ID = b.ID. In theory, you could have more than one FlagName per ID. Thus a 1:M relation. You could have a flag '# of kids', 'Dead', '# of times attacked by killer rabbits', etc...

    I found a solution to my problem, which looks like this :

    SELECT TF.ID, TF.First_Name, TF.NOM, TF.ADRESSE,
    (SELECT FlagValue FROM Test_Frank_DETAIL TFD WHERE TFD.ID = TF.ID AND FlagName = 'MARRIED') AS MR,
    (SELECT FlagValue FROM Test_Frank_DETAIL TFD WHERE TFD.ID = TF.ID AND FlagName= 'DEAD') AS GE,
    (SELECT FlagName FROM Test_Frank_DETAIL TFD WHERE TFD.ID = TF.ID AND EVEN = 'Number of toes') AS AA
    FROM Test_FRANK TF

    I just don't think it's either practical or optimized. Do you think I could create an updatable view with such a query?

    Frank a.k.a. brouminator

  5. #5
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by brouminator
    Unfortunately, you didn't. Creating a view is not a problem, it's finding the right query for the view to have, that is bugging me.

    I'm not looking for a query with a.ID = b.ID. In theory, you could have more than one FlagName per ID. Thus a 1:M relation. You could have a flag '# of kids', 'Dead', '# of times attacked by killer rabbits', etc...

    I found a solution to my problem, which looks like this :

    SELECT TF.ID, TF.First_Name, TF.NOM, TF.ADRESSE,
    (SELECT FlagValue FROM Test_Frank_DETAIL TFD WHERE TFD.ID = TF.ID AND FlagName = 'MARRIED') AS MR,
    (SELECT FlagValue FROM Test_Frank_DETAIL TFD WHERE TFD.ID = TF.ID AND FlagName= 'DEAD') AS GE,
    (SELECT FlagName FROM Test_Frank_DETAIL TFD WHERE TFD.ID = TF.ID AND EVEN = 'Number of toes') AS AA
    FROM Test_FRANK TF

    I just don't think it's either practical or optimized. Do you think I could create an updatable view with such a query?

    Frank a.k.a. brouminator
    That is why I mentioned creating multiple views. This is where you need to get a little creative in writing select statements to do your work for you.

    select 'CREATE OR REPLACE VIEW '||FLAGNAME||'_CUST_VW AS SELECT ID, FLAG FLAGVALUE AS '||FLAGNAME||' FROM TABLE B
    WHERE FLAGNAME = '||CHR(39)||FLAGNAME||CHR(39)||' ORDER BY ID;'
    FROM TABLE B
    GROUP BY FLAGNAME
    ORDER BY FLAGNAME

    SPOOL <PATHNAME>\CREATE_VIEWS.SQL
    /
    SPOOL OFF
    @<PATHNAME>\CREATE_VIEWS.SQL

    Do a
    SELECT VIEW_NAME
    FROM ALL_VIEWS
    WHERE VIEW_NAME LIKE '%CUST_VW%'


    and then incorporate it in some master view. You may have to do some standardization of the data in table B if the end users are doing hand entries of the FLAGNAME value such as using "married", "marital_stat" & "Marriage", or "Child1", "Dependent1" and "Dependent 1" but you really need to do that no matter what.

    This way you can run the create views query multiple times
    and edit the master view by adding in the new fields and view id's as needed.

    Then you can do your select as
    SELECT ID, Name, Address, Married, "# of times attacked by killer rabbits"
    FROM TABLEA, Married_Cust_Vw, "# of times attacked by killer rabbits"
    WHERE TABLEA.ID=Married_Cust_Vw.ID(+)
    and TABLEA.ID="# of times attacked by killer rabbits".ID(+)
    ORDER BY ID
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

Posting Permissions

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