Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    73

    Cool Unanswered: Help with Select Query.

    Hi ,
    I am in a scenario where I have to perform a select on 2 tables . Its like

    Table 1 Table 2

    ---------------- --------------
    ID || Name ID || Amount
    ---------------- -----------------
    0 || ABC 0 || 50000
    1 || XXX 1 || 0
    2 || ADO 2 || 0
    3 || ZZZ 3 || 10000
    4 || ddd 4 || 0
    5 || xxxx
    6 || test

    I have to select all values from table 1 whose amount is zero in table 2 along with all the values who are missing in table 2 as well . I dont need to select any values which are included in table 2 and whose amount is >0.
    The constraint is on the id on the table in Table 2.

    I tried using an outer join on my query, but that is returning all results from table 1 which have amount > 0 as well because they are existing in Table 1 .

    Here is my query , any clue guys ?


    select a.* , b.* from administrator.clmplaheader as a
    LEFT OUTER JOIN administrator.clmclmentry as b on
    (a.CLMPLANO = b.clmplano AND b.clmlossamt <=0)
    where
    a.clmpladate between '04-02-2009' and '07-03-2009' ;

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Did you try moving "b.clmlossamt <=0" to WHERE clause ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Mar 2009
    Posts
    73

    Hi.

    thanks for the reply ,

    I tried that and its just returning me those records which have amount = 0 and not all those which are in table 1 and not in table2. Similar is the case with and INNER join.

    Its got a bit confusing to me

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    This one ???
    Code:
    with t1(id,name) as
    (
    values(0,'ABC'),
    (1,'XXX'),
    (2,'ADO'),
    (3,'ZZZ'),
    (4,'ddd'),
    (5,'xxxx'),
    (6,'test')
    ),
    t2(id,amt) as
    (
    values(0,50000),(1,0),(2,0),(3,10000),(4,0)
    )
    select a.* , b.* from t1 as a
    LEFT OUTER JOIN t2 as b on
    a.id = b.id
    where amt<=0 or amt is null
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jun 2009
    Posts
    3
    select a.* , b.*
    from
    administrator.clmplaheader as a
    LEFT OUTER JOIN administrator.clmclmentry as b on (a.CLMPLANO = b.clmplano)
    where
    a.clmpladate between '04-02-2009' and '07-03-2009'
    AND COALESCE(b.clmlossamt ,0) <= 0;

Posting Permissions

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