Results 1 to 3 of 3

Thread: sUMMARY tABLE

  1. #1
    Join Date
    Feb 2005
    Posts
    76

    Unanswered: sUMMARY tABLE

    Trying to create a summary table of current product table, so that they is only 1 line for each product (even if the product is in both warehouses - in this case take table for warehouse 1) Therefore standard case statement becomes:

    CASE WHEN exists (select product from stock where warehouse = '02' and product = stock.product and product in (select product from stock where warehouse = '01'))
    ProductDescription = (select distinct long_description from stock where warehouse = '01' and product = stock.product)
    ELSE
    ProductDescription = (select distinct long_description from stock where product = stock.product and (warehouse = '01' or warehouse = '02'))
    END

    Is there another way of writting this, instead of repeating the above code for each column in the table?

    THANKS

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    is there another way of doing this? probably, if i knew what you were doing

    please say in english what the query is supposed to do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2007
    Posts
    2
    How about the following (yes Select * isn't the best but you can add the column names at your convenience)

    SELECT * FROM stock where warehouse = '01'
    UNION
    SELECT * FROM stock where warehouse = '02' AND product NOT IN (SELECT product FROM stock where warehouse = '01')

    In essence you are taking all products from warehouse 1 and adding the extra products from warehouse 2 that are unique to that warehouse.

Posting Permissions

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