Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005

    Unanswered: add a column if product is in discrepancies table (was "sql query")

    I have a transaction table (which contains details of all products on all orders)
    and is have a discrepancies table which includes details on products that couldnt been found (hence error in stock count)...

    Now I want to write a query returning details of all products from a specfic orders.. pretty simple

    SELECT product, description, qty
    where order_no = 'xxx'

    but i also want to add an extra boolean column (true if product is in discrepancies table and false if product is not in discrepancies table...will become checkbox at front end)... im not sure how to write this bit....

    I know it would a transaction table LEFT JOIN to discrepancies table, but I do not know how to write syntax to add extra column...

    Be garteful for any help


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    select product
         , description
         , qty
         , case when exists
                ( select 937
                    from discrepancies
                   where product_id = t.product_id )
                then 'oui'
                else 'non'
             end            as discrepancy_exists
      from TRANSACTIONS  as t
     where order_no = 'xxx' | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    thanks worked like a treat

Posting Permissions

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