Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: SQL sets query

  1. #1
    Join Date
    Mar 2003
    Location
    NJ/NY
    Posts
    17

    SQL sets query

    PLEASE SEE my update (post #3) for a better simpler explanation!!!!!

    Hi,
    I need a query which is basically

    (query 1)
    Except
    (query 2)

    SQL server supports
    (query 1)Union (query 2)
    but I can't get the Except to work.

    Alternatively,
    I also tried to implement it by using the "NOT IN" but didnt work.
    Background: 3 tables, accnts, Opty, Opty_postn
    I want
    the accounts who have 1 or more opty's at 100% (sold) before 1/1/2002 but zero opty's at 100% after 12/31/2001.
    accnts has fields ID & name, ROW_ID
    opty has fields acct_id, prob, ROW_ID
    opty_postn has fields opty_id, close_dt

    My query:
    SELECT accnt.name
    FROM accnt
    WHERE
    accnt.ROW_ID IN
    (
    SELECT acct_id
    FROM opty
    where prob = 100
    AND opty.ROW_ID IN
    (SELECT opty_id
    FROM opty_postn
    WHERE close_dt<1/1/2002)
    )
    AND accnt.ROW_ID NOT IN
    (
    SELECT acct_id
    FROM opty
    where prob = 100
    AND opty.ROW_ID IN
    (SELECT opty_id
    FROM opty_postn
    WHERE close_dt >12/31/2001)
    )
    AND accnt.ROW_ID = opty.accnt_ID

    my intent was
    select account.names
    where id
    is in set(prob = 100 & sale before 1/1/02)
    but not in ( prob = 100 & sale after 21/31/01)
    SO ,the accounts which have sales both before and after get counted. But I want only those accoutns which have sales only before 1/1/02.
    I hope i explained this right.
    Thanks in advance for your help.

    Ash.
    Last edited by ashish9393; 04-21-2003 at 06:24 PM.

  2. #2
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    I am pretty confused by those id,rowid,opty_id....

    SELECT accnt.name FROM accnt WHERE accnt.ROW_ID IN (SELECT acct_id FROM opty......)AND accnt.ROW_ID NOT IN
    (SELECT acct_id from opty .....)

    It seems you are putting where condition on "accnt.row_id" by choosing "acct_id" from opty......

    If your accnt.row_id does exist in the acct_id you choose from opty...of course there will be some results coming out.

    But if accnt.row_id doesnt have any value existing the acct_id you choose.You wont be able to get anything returned.

    Maybe you should check the column name you put in the query is correct or not first.

  3. #3
    Join Date
    Mar 2003
    Location
    NJ/NY
    Posts
    17
    The column names and all are correct. I verified this by running the query in smaller parts. I get the right individual results but as soon I use the NOT I get nothing.
    The way I understand NOT is the if there are two sets A & B
    A NOT B should all elements in A that are not in B. My qurey in algo like lang looks like this

    select account name
    from account table
    where
    account.row_id IN
    (
    select opty.acct_id
    where
    probability = 100%
    and close date < 1/1/2002
    )
    and account.row_id NOT IN
    (
    select opty.acct_id
    where
    probability = 100%
    and close date >12/31/2001
    )

    the first subquery gives me 92 rows
    the second gives me 195 rows.
    if I dont use the "NOT', it gives me 46.
    But whenever I use NOT i get 0 rows.
    I have manually inspected that there are rows in first one that are absent in the other.
    thanks,
    Ash.

  4. #4
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201

    Talking

    Hello....

    You made a small mistake that I didnt realize either.
    Instead of "AND" change it to "OR"

    Because the two groups inside of open brackets are disjoint.If you use AND.There wont be any joined set left.

    Hee hee,I took probability and Random process.Professor taught us this concept.^_^


    If you make sure everything,simply change the code like following:

    select account name
    from account table
    where
    account.row_id IN
    (
    select opty.acct_id
    where
    probability = 100%
    and close date < 1/1/2002
    )
    or account.row_id NOT IN
    (
    select opty.acct_id
    where
    probability = 100%
    and close date >12/31/2001
    )

  5. #5
    Join Date
    Mar 2003
    Location
    NJ/NY
    Posts
    17

    Unhappy

    Thanks for the suggestion Claire, but it didn't work. I get 92 rows using the OR; which is strange since the 2 sets have common and unique elements so the number should be higher than 195.
    Claire, the two sets are not disjoint. since there are customers who bought before and after 12/31/01.
    My aim is to find customers that stopped doing business after 12/31/2001.
    So I need to weed out those customers that bought after 12/31/2001. SO the OR won't work. Another way to wowd it would be, select customers who bought before 1/1/02 and then from them weed out those who ALSO bought after 12/31/01.

    thanks,
    ash.

  6. #6
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    There are few way to solve it.Here is one way:

    SELECT accnt.name
    FROM accnt
    WHERE
    accnt.ROW_ID IN
    (
    SELECT acct_id
    FROM opty
    where prob = 100
    AND opty.ROW_ID IN
    (SELECT opty_id
    FROM opty_postn
    WHERE close_dt<1/1/2002)
    )
    AND accnt.ROW_ID IN
    (
    SELECT acct_id
    FROM opty
    where prob = 100
    AND opty.ROW_ID IN
    (SELECT opty_id
    FROM opty_postn
    WHERE close_dt <12/31/2001)
    )


    I will find some more ways.I believe there are some logical problem in your query.

  7. #7
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    Give this a try too.

    SELECT accnt.name
    FROM accnt
    WHERE
    accnt.ROW_ID IN
    (
    SELECT acct_id
    FROM opty
    where prob = 100
    AND opty.ROW_ID IN
    (SELECT opty_id
    FROM opty_postn
    WHERE close_dt<1/1/2002)
    )
    AND accnt.ROW_ID NOT IN
    (
    SELECT acct_id
    FROM opty
    where prob = 100
    AND opty.ROW_ID IN
    (SELECT opty_id
    FROM opty_postn
    WHERE close_dt >12/31/2001 and close_dt < 1/1/2002)
    )

  8. #8
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    According to the statistics you mentioned few posts ago.

    1.This returns 92 rows.(before 1/1/2002)

    select account name
    from account table
    where
    account.row_id IN
    (
    select opty.acct_id
    where
    probability = 100%
    and close date < 1/1/2002
    )

    2.This return 195 rows ,(Logically speaking,this is eaqul to before 12/31/2001)

    select account name
    from account table
    where account.row_id NOT IN
    (
    select opty.acct_id
    where
    probability = 100%
    and close date >12/31/2001
    )

    Here is the unrational portion.
    If all the condition are the same ,only the date condition is different.

    The later the date is ,the more the rows should be returns.

    Why the 1st one is less than 2nd one??How is this possible?

  9. #9
    Join Date
    Mar 2003
    Location
    NJ/NY
    Posts
    17
    Claire,
    Not sure if you noticed but the dates are 12/31/01 & 1/1/02.
    12/31/01 is used when I use >
    & 1/1/02 is used when I use <.
    <1/1/02 is same as <=12/31/01.
    So in both sub-queries the cut-off is same, midnight 12/31/01. When I say >12/31/01 I mean all sales after year 2001. I am not looking at a one day difference.

  10. #10
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    Sorry I didnt realize that...I dont think your query is wrong.I can do some explanation why it returns zero all the time whenever you use not in.

    say you have 2 circle one represent 1st condition.the other one represent the 2nd condition.

    What you want is the joint part of the [1st circle] and the [complemental part of 2nd circle].Accidently there is no joint between them,that's why you got zero.

    But there is joint between 1st circle and 2nd circle.

    Could you provide some data?

    I am sure there is some logical mistake in it.

  11. #11
    Join Date
    Mar 2003
    Location
    NJ/NY
    Posts
    17
    Claire,
    As mentioned earlier, I have manually inspected the data and each sub-query produces data which has common and uncommon entries with the other.

    I also have numbers,
    assume,
    circle 1 = A
    circle 2 = B
    and a big circle eclosing everything = S

    A = 92
    B = 195
    A AND B = 46
    A OR B = 241 .... (195 + 46)
    So A AND (NOT B) should be 46. (92 - 46)
    but i get 0.
    B AND (NOT A) should be 149. (195 -46)
    But I get 0.

    Anther way is
    A AND (NOT B) = (A OR B) - B
    = 241 - 195
    = 46.

  12. #12
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    I made some modification...please pay close attention....

    A:
    account.row_id IN
    (
    select opty.acct_id
    where
    probability = 100%
    and close date < 1/1/2002
    )

    B:
    where account.row_id NOT IN
    (
    select opty.acct_id
    where
    probability = 100%
    and close date >12/31/2001
    )

    circle 1 = A
    circle 2 = B



    1.the first subquery gives me 92 rows
    A = 92

    2.the second gives me 195 rows.
    B = 195

    3.if I dont use the "NOT', it gives me 46.
    A AND "Not" B = 46

    4.But whenever I use NOT i get 0 rows.
    A AND B = 0

    5.I get 92 rows using the OR
    A OR B = A = 92

    Conclusion:
    It's not possible these five condition is in the same case!

    Could you provide some rows from those tables?Maybe we could do some simulation instead of discussing concept.

  13. #13
    Join Date
    Mar 2003
    Location
    NJ/NY
    Posts
    17
    Claire,

    B:
    (
    select opty.acct_id
    where
    probability = 100%
    and close date >12/31/2001
    )
    and not

    where account.row_id NOT IN
    (
    select opty.acct_id
    where
    probability = 100%
    and close date >12/31/2001
    )

    the NOT is out side B.

    And from your last post, 3, & 5 are wrong.
    In 5
    A OR B = 241.
    A AND (NOT B) should be 46 but i get 0.

    The prob in giving sample data is that thos data is pulled out from 5 tables. LEt me do some work to simplify them and then i will give u the data.

  14. #14
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201

    Wink

    Sure,I will stand by here.

  15. #15
    Join Date
    Mar 2003
    Location
    NJ/NY
    Posts
    17
    Claire,
    I prepared some test data. And unfortunately , the test data works. SO I tried to make the data as close to the real thing as possible but it still works. NOt sure about next steps.

Posting Permissions

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