Results 1 to 2 of 2

Thread: SQL Statement - WHERE Clause Conditional Logic

  1. #1
    Join Date
    Mar 2009
    Posts
    2

    SQL Statement - WHERE Clause Conditional Logic

    I can't believe I need help with this one, but regrettably I do !!

    I have three tables ... One, Two and Three, which represent 3 dimensions.

    They are populated with 1, 2 and 2 records respectively.

    I use these tables to create a product table OneTwoThree at a point in time with 4 records (1 * 2 * 2).

    I created a view to monitor the dimension tables directly, with the intention of finding new dimensions (when they are created in either One, Two or Three) and inserting the new records into table OneTwoThree.

    My problem is I cannot successfully identify the new records.

    Now to my code ...

    CREATE TABLE [ald].[tblOne](
    [id1] [varchar](5) NULL
    ) ON [PRIMARY]


    id1:
    day1


    CREATE TABLE [ald].[tblTwo](
    [id2] [varchar](5) NULL
    ) ON [PRIMARY]


    id2:
    prd1
    prd2


    CREATE TABLE [ald].[tblThree](
    [id3] [varchar](5) NULL
    ) ON [PRIMARY]

    id3:
    str1
    str2


    Table OneTwoThree is populated using the view ...

    create view [ald].[vwOneTwoThree]
    as
    select *
    from (select * from bi_development.ald.tblOne) One cross join
    (select * from bi_development.ald.tblTwo) Two cross join
    (select * from bi_development.ald.tblThree) Three


    I then add a new member to the tbleOne dimension ... day2

    I now want to find the new dimension permutations that need to be added to bring the OneTwoThree table up-to-date.

    View Results:
    select * from ald.vwOneTwoThree

    id1 id2 id3
    day1 prd1 str1
    day2 prd1 str1 < new record
    day1 prd2 str1
    day2 prd2 str1 < new record
    day1 prd1 str2
    day2 prd1 str2 < new record
    day1 prd2 str2
    day2 prd2 str2 < new record


    Table Results:
    select * from ald.tblOneTwoThree

    id1 id2 id3
    day1 prd1 str1
    day1 prd2 str1
    day1 prd1 str2
    day1 prd2 str2


    select v123.id1,v123.id2,v123.id3,t123.id1,t123.id2,t123. id3
    from ald.vwOneTwoThree v123 left outer join
    ald.tblOneTwoThree t123 on
    v123.id1 = t123.id1 and
    v123.id2 = t123.id2 and
    v123.id3 = t123.id3


    id1 id2 id3 id1 id2 id3
    day1 prd1 str1 day1 prd1 str1
    day2 prd1 str1 NULL NULL NULL
    day1 prd2 str1 day1 prd2 str1
    day2 prd2 str1 NULL NULL NULL
    day1 prd1 str2 day1 prd1 str2
    day2 prd1 str2 NULL NULL NULL
    day1 prd2 str2 day1 prd2 str2
    day2 prd2 str2 NULL NULL NULL


    I want to isolate the new records .... by selecting only those records with NULL

    select v123.id1,v123.id2,v123.id3,t123.id1,t123.id2,t123. id3
    from ald.vwOneTwoThree v123 left outer join
    ald.tblOneTwoThree t123 on
    v123.id1 = t123.id1 and
    v123.id2 = t123.id2 and
    v123.id3 = t123.id3 and
    t123.id1 is null and
    t123.id2 is null and
    t123.id3 is null


    ... yet the query returns everything ...

    id1 id2 id3 id1 id2 id3
    day1 prd1 str1 NULL NULL NULL
    day2 prd1 str1 NULL NULL NULL
    day1 prd2 str1 NULL NULL NULL
    day2 prd2 str1 NULL NULL NULL
    day1 prd1 str2 NULL NULL NULL
    day2 prd1 str2 NULL NULL NULL
    day1 prd2 str2 NULL NULL NULL
    day2 prd2 str2 NULL NULL NULL


    This approach works for 2 tables but I assume I need a different approach for 3.

    Any help would be much appreciated !!

  2. #2
    Join Date
    Mar 2009
    Posts
    2

    Red face Update ... problem solved !

    My problem is solved ...

    select v123.id1,v123.id2,v123.id3,t123.id1,t123.id2,t123. id3
    from ald.vwOneTwoThree v123 left outer join
    ald.tblOneTwoThree t123 on
    v123.id1 = t123.id1 and
    v123.id2 = t123.id2 and
    v123.id3 = t123.id3
    where
    t123.id1 is null and
    t123.id2 is null and
    t123.id3 is null


    I can't believe I didn't spot it sooner.

    Apologies to any experts who have wasted any time on this one.

Posting Permissions

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