Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2015
    Posts
    1

    Unanswered: Join two tables, displaying missing parameters as NULLs.

    Hello folks,

    given two tables PETS and PET_PARAMETERS I need to join them together for a view VIEW_PETS.

    PETS
    ----------------------
    .PET_ID..|.PET_TYPE.|
    ----------------------
    ......1.....|.....DOG.....|
    ......2.....|.....CAT.....|
    ......3.....|....FROG....|
    ----------------------

    PET_PARAMETERS
    -------------------------------------------------------------------
    .PARAMETER_ID.|.PET_ID.|.PARAMETER_NAME.|.PARAMETE R_VALUE.|
    -------------------------------------------------------------------
    ..........1..........|.....1.....|............AGE. .........|............12............|
    ..........2..........|.....1.....|...........NAME. ........|............WULFI.......|
    ..........3..........|.....2.....|............AGE. .........|............10............|
    ..........4..........|.....2.....|...........NAME. ........|............KITTY.......|
    ..........5..........|.....3.....|............AGE. .........|.............8.............|
    -------------------------------------------------------------------

    Desired shape of view - VIEW_PETS
    ---------------------------------
    .PET_ID.|.PET_TYPE.|.PET_NAME.|
    ---------------------------------
    .....1.....|.....DOG.....|...WULFI....|
    .....2.....|.....CAT.....|...KITTY.....|
    .....3.....|....FROG....|...NULL......|
    ---------------------------------

    The tricky part is to have the FROG included in resulting view. It does not have the NAME - parameter, but I need it to appear as NULL.

    Code:
    -- example script may differ from ANSI-SQL slightly 
    CREATE TABLE PETS (PET_ID INTEGER, PET_TYPE VARCHAR2(10));
    ALTER TABLE PETS ADD CONSTRAINT PETS_PK PRIMARY KEY (PET_ID);
    INSERT INTO PETS (PET_ID, PET_TYPE) VALUES  (1, 'DOG');
    INSERT INTO PETS (PET_ID, PET_TYPE) VALUES  (2, 'CAT');
    INSERT INTO PETS (PET_ID, PET_TYPE) VALUES  (3, 'FROG');
    
    CREATE TABLE PET_PARAMETERS (PARAMETER_ID INTEGER, PET_ID INTEGER, PARAMETER_NAME VARCHAR2(10), PARAMETER_VALUE VARCHAR(10));
    ALTER TABLE PET_PARAMETERS ADD CONSTRAINT PET_PARAMETERS_PK PRIMARY KEY (PARAMETER_ID);
    ALTER TABLE PET_PARAMETERS ADD CONSTRAINT PETPARAMETERS_PETID_FK FOREIGN KEY (PET_ID) REFERENCES PETS (PET_ID);
    INSERT INTO PET_PARAMETERS (PARAMETER_ID, PET_ID, PARAMETER_NAME, PARAMETER_VALUE) VALUES (1, 1, 'AGE',  '12');
    INSERT INTO PET_PARAMETERS (PARAMETER_ID, PET_ID, PARAMETER_NAME, PARAMETER_VALUE) VALUES (2, 1, 'NAME', 'WULFI');
    INSERT INTO PET_PARAMETERS (PARAMETER_ID, PET_ID, PARAMETER_NAME, PARAMETER_VALUE) VALUES (3, 2, 'AGE',  '10');
    INSERT INTO PET_PARAMETERS (PARAMETER_ID, PET_ID, PARAMETER_NAME, PARAMETER_VALUE) VALUES (4, 2, 'NAME', 'KITTY');
    INSERT INTO PET_PARAMETERS (PARAMETER_ID, PET_ID, PARAMETER_NAME, PARAMETER_VALUE) VALUES (5, 3, 'AGE',  '8');
    
    CREATE VIEW VIEW_PETS (PET_ID, PET_TYPE, PET_NAME) 
    AS 
      SELECT PETS.PET_ID,
        PETS.PET_TYPE,
        PET_PARAMETERS.PARAMETER_VALUE AS PET_NAME 
      FROM PETS
      INNER JOIN PET_PARAMETERS
      ON PETS.PET_ID = PET_PARAMETERS.PET_ID 
      WHERE PET_PARAMETERS.PARAMETER_NAME = 'NAME';
    
    SELECT * FROM PETS;
    SELECT * FROM PET_PARAMETERS;
    SELECT * FROM VIEW_PETS;
    Please give me some hints, I have really no idea where to start

  2. #2
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Use an OUTER JOIN to get rows without hits in the other table. LEFT [outer] JOIN in this case.

    CREATE VIEW VIEW_PETS (PET_ID, PET_TYPE, PET_NAME)
    AS
    SELECT PETS.PET_ID,
    PETS.PET_TYPE,
    PET_PARAMETERS.PARAMETER_VALUE AS PET_NAME
    FROM PETS
    LEFT JOIN PET_PARAMETERS
    ON PETS.PET_ID = PET_PARAMETERS.PET_ID AND PET_PARAMETERS.PARAMETER_NAME = 'NAME';



    Btw, it's a bad idea to store data like PARAMETER_NAME.|.PARAMETE R_VALUE, use separate columns for different types of values. (Or even separate tables.)

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it wouldn't be 'tricky' if the design was properly normalised. Instead of using EAV use a normalised schema
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    EAV or normalized, still need the outer join.
    I understand the want for EAV, but there is a lot to be said for proper design of your database as well. You have pet info, so why not have the column age, name, breed, etc... Rather than set yourself up for having to filter thru all of these rows to find particular column values?
    Dave

Posting Permissions

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