Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004

    Unanswered: Need help with a query

    I have four columns: wbs1, wbs2, wbs3, amount

    I need to find out if there are any records that have the same wbs1, wbs2 and if they have both null and not null values in wbs3.
    For example:

    wbs1    wbs2    wbs3     amount
    123       12      1        0         
    123       12             1000
    I had tried doing this:
    Select * from (Select wbs1. wbs2, wbs3, amount from lb
    where (WBS3 = '') AND (Amount <> 0)as A Join
    (Select Wbs1, wbs2, wbs3, amount
    from lb
    where wbs3 != '') as b 
    ON a.wbs1 = b.wbs1 and a.wbs2 = b.wbs2 and a.wbs3 = b.wbs3 and a.amount = b.amount
    any ideas would be great.

    Last edited by lauramccord; 11-17-04 at 17:55.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    these columns seem strangely familiar, where have i seen them before?
    select wbs1
         , wbs2
         , wbs3
         , amount
      from yourtable as x
     where 3 =
         ( select sum(distinct
              case when wbs3 is null 
                   then 1 else 2 end )
             from yourtable
            where wbs1 = x.wbs1
              abd wbs2 = x.wbs2 )
    note this is a correlated subquery, joining each row of the table in the outer query to all rows (including itself) with the same wbs1 and wbs2

    the case expression will evaluate to either 1 or 2

    then the DISTINCT will resolve all of them to either 1 or 2 values

    thus the sum will be 1, 2, or 3

    sweet, eh? | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Oh wonderful, thanks so much for your help.


Posting Permissions

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