Results 1 to 9 of 9

Thread: more than 1 pk

  1. #1
    Join Date
    Feb 2005
    Posts
    76

    Unanswered: more than 1 pk

    The product table 2 pk... product and whse

    Now I want all list of the products in an order, not including 1 product:

    select * from TRANSACTIONS
    where order= 'xxx'
    and product not in
    (select product from TRANSACTIONS where order= '456886' and product = 'ZYH' and whse = 'AB')

    But this will be incorrect if the order includes two products with the same product (but different whse)... how do I correct the above query so that it only discludes the 1 product??

    Thanks again

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    the language is not clear to me neither the sql.....
    however, i do not unserstand why a simple
    Code:
    select * from TRANSACTIONS 
    where order= 'xxx'  
    and product <> 'ZYH'
    will not work?

  3. #3
    Join Date
    Feb 2005
    Posts
    76
    1 order may have 2 lines with the same product BUT DIFFERENT whse...

    I want to disclude the line where "product = 'ZYH' and whse = 'AB'"... eg if the line containd
    product = 'ZYH' and whse = 'AB'
    product = 'ZYH' and whse = 'CD'

    I would only want to disclude the 1st line, and not the second row

  4. #4
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    Code:
    select * from TRANSACTIONS
    where order= 'xxx' 
    and product <> 
    (select product from TRANSACTIONS where order= '456886' and product = 'ZYH' and whse = 'AB') 
    and whse <> 
    (select whse from TRANSACTIONS where order= '456886' and product = 'ZYH' and whse = 'AB')
    but if u r knowing the values of product and/or whse, u should be supplying the values directly without using select......

  5. #5
    Join Date
    Feb 2005
    Posts
    76
    no this wodnt wrk...

    That query will disclude all lines with the product and all the products with whse 'AB'... i only want that specific 1...

  6. #6
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    right... the above will eliminate other rows with same product or whse. here is the modified sql
    Code:
    select * from TRANSACTIONS
    where order= 'xxx' 
    and product + whse <> 
    (select product+whse from TRANSACTIONS where order= '456886' and product = 'ZYH' and whse = 'AB')
    u need some casting if product or whse are char type

  7. #7
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Try Distinct....

    Code:
    select * from TRANSACTIONS
    where order= 'xxx' 
    and product in 
    (select distinct product from TRANSACTIONS where order= '456886' and product = 'ZYH' and whse = 'AB')
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  8. #8
    Join Date
    Feb 2005
    Posts
    76
    i dun fink Im explaining it right...

    The sub query is correct.... maybe my whole query is wrong

    Start again:

    product table PK: prodcut AND whse

    I want to return a list of all products in a certain order (FK from orders table)
    EXCEPT for 1 product line, where product = 'x' and whse = '1'


    eg
    Order 1
    product x whse 1(order 1)
    product x whse 2
    product y whse 1
    product z whse 1
    product z whse 2

    The query should return
    product x whse 2
    product y whse 1
    product z whse 1
    product z whse 2

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select * from TRANSACTIONS
    where order= 'xxx'
    and not ( product = 'ZYH' and whse = 'AB')
    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
  •