Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    76

    Unanswered: HAVING clause is a case statement???

    i have wrote a query which compares two tables, returning anywhere the qty is not the same in each table:

    (simple ex)

    Select order_no
    from table1
    inner join table2
    on table1.order_no = table2.order_no
    group by order_no
    having table1.Qty<> table2.Qty

    BUT... I need to add a table3, where there maybe (or may not be enters - thus a left join). If there is an entry in table3 then use qty in table3 and not table1... so having becomes:

    CASE WHEN table1.Qty<> table3.Qty
    THEN table3.Qty<> table2.Qty
    ELSE table1.Qty<> table2.Qty END

    but how do i actually write this?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    perhaps if you would care to explain what you're doing?

    are you comparing individual Qty values, or the SUMs?

    because the HAVING clause may refer only to columns in the GROUP BY or to aggregate functions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Posts
    76
    Quote Originally Posted by r937
    perhaps if you would care to explain what you're doing?

    are you comparing individual Qty values, or the SUMs?

    because the HAVING clause may refer only to columns in the GROUP BY or to aggregate functions

    Sorry I am trying to compare Sum(qty) for each product in an order (product maybe in the order more than 1ce) I am trying to retrieve any product lines where Sum qties in table1 and table2 are not the same.

    However, if stock was not found, then an allocated qty is recorded in table 3...so in this case I want to compare qtyies in table3 and table2
    ???

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select t1.order_no
         , t1.sumqty
         , t2.order_no
         , t2.sumqty
      from (
           select order_no 
                , sum(qty) as sumqty
             from table1
           group
               by order_no
           ) as t1
    full outer
      join (
           select order_no 
                , sum(qty) as sumqty
             from table2
           group
               by order_no
           ) as t2
        on t2.order_no = t1.order_no
       and t2.sumqty <> t1.sumqty
    that's the general strategy -- do your sums in derived tables

    for table 3, you're on your own
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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