none
Except Statement

    Question

  • All,

    I'm working on a fairly simple query, but it's giving me a headache. The query is:

    SELECT product_name from TABLE products WHERE origin = 'USA'

    EXCEPT

    SELECT product_name from TABLE products WHERE perishable = 'yes'

    the above query works, but when I modify to select the column perishable as well, it seems that the except statement does work as the query returns where products in the USA and are perishable...help?

    Regards,

    Sean

    • Moved by Papy Normand Monday, February 27, 2012 5:58 PM Not related to SQL Server Data Access but to T-SQL (From:SQL Server Data Access)
    Monday, February 27, 2012 2:30 PM

Answers

All replies

  • All,

    I'm working on a fairly simple query, but it's giving me a headache. The query is:

    SELECT product_name from TABLE products WHERE origin = 'USA'

    EXCEPT

    SELECT product_name from TABLE products WHERE perishable = 'yes'

    the above query works, but when I modify to select the column perishable as well, it seems that the except statement does work as the query returns where products in the USA and are perishable...help?

    Regards,

    Sean

    • Merged by KJian_ Wednesday, February 29, 2012 6:24 AM duplicate
    Monday, February 27, 2012 2:03 PM
  • Not sure why you want to use EXCEPT

    You can do

    SELECT product_name, perishable from  products WHERE origin = 'USA' AND perishable <> 'yes'
    

    Monday, February 27, 2012 2:27 PM
  • Hi,

    Your query returns all product_name from your table where origin is USA and removes/except all rows where perishable is yes. EXCEPT, INTERSECT operators need the same number of columns on both sides. If you add perishable column to both queries,

    SELECT product_name, perishable  from TABLE products WHERE origin = 'USA'
    EXCEPT
    SELECT product_name, perishable  from TABLE products WHERE perishable = 'yes'

    your result will return: all rows where origin is USA and removes/except all rows where perishable is yes.

    There might not be any difference. If you post some sample data with teh expected result, I'll be able to provide much better help.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    Monday, February 27, 2012 3:01 PM
  • Please do not cross post

    http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/7eabf727-46fa-4e90-9e05-43b07731699d

    Not sure why you want to use EXCEPT

    You can do

    SELECT product_name, perishable from  products WHERE origin = 'USA' AND perishable <> 'yes'
    Monday, February 27, 2012 3:15 PM
  • here is some sample data:

    product_name origin perishable
    A USA yes
    B FRA yes
    C USA yes
    D GER yes
    E USA no
    F RUS no
    G CAN no
    H USA no
    I USA no
    J FRA yes
    K GER yes
    L CAN yes

    My understanding is that the second query (the one selecting both product_name and perishable) would return products A and C, correct?

    Regards,

    Sean

    Monday, February 27, 2012 3:18 PM
  • Not sure what output you desired

    if you want both origin USA and perishable YEs  then

    SELECTproduct_name,perishable from  products WHERE origin ='USA'AND perishable ='yes'

    if you want both origin USA and perishable No  then

    SELECTproduct_name,perishable from  products WHERE origin ='USA'AND perishable ='No'

    if you are inclined to use EXCEPT

    --gives all product that have origin 'USA; and are perishable (i.e.  perishable = 'Yes')
    --excludes non perishable product with US origin
    select productname, perishable from products where origin = 'USA' and perishable = 'Yes'
    except
    select productname, perishable from products where origin = 'USA' and perishable = 'No'




    • Edited by Chirag Shah Monday, February 27, 2012 3:41 PM
    Monday, February 27, 2012 3:22 PM
  • products was a bad example, let me try again...sorry

    user device
    A 1
    X 2
    B 2
    C 1
    D 2
    E 2
    F 1
    G 1
    H 1
    I 1
    J 1
    K 1
    L 1
    W 1
    H 1
    A 2
    Z 1
    M 1
    C 2

    I need users with device 1, but not device 2.

    Monday, February 27, 2012 3:59 PM
  • Do you want to select only Users, that have all Device = 1 and none of them have Device = 2? If so, this is typical relational division problem.

    The solution which is good for performance may be

    select Userfrom UserDevices
    GROUP BY User
    having MAX(case when Device = 2 then 1 else 0 end) = 0 

    This tricky HAVING clause excludes all users that may have Device = 2.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, February 27, 2012 4:08 PM
  • Hello SeanP79,

    Your thread is not related to SQL Data Access but Transact-SQL. It is why i will move to the Transact-SQL forum where the replies will be more useful and will interest  more people.

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Monday, February 27, 2012 5:56 PM
  • Hello,

    Move done. I hope you will receive a quick and full answer to your problem.

    Just a little question : what is the data type of the perishable column ? Maybe i am wrong , but i think it would be defined as bit not as char ( it would need less space )

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Monday, February 27, 2012 6:02 PM