Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2006
    Posts
    39

    Unanswered: SQL Query help. DIVIDE

    this is from a SQL playbook
    i don't understand the logic of DIVIDE when you use more than one NOT EXISTS

    such as this query

    select distinct itemname from qdel del where not exists (select * from qspl
    where not exists (select * from qitem where itemtype='n' and not exists
    (select * from qdel where qdel.itemname=qitem.itemname
    and qdel.splno=qspl.splno)) and not exists (select * from qdel
    where qdel.itemname = del.itemname and qdel.splno=qspl.splno))

    i don't understand how can NOT EXISTS code brings out the results of the existing rows in those not exists lines
    like it says NOT EXISTS itemtype 'n' etc. but that is the results!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    who wrote that monstrous query? what is it supposed to do, in words?

    and what is an SQL "playbook" please? i want to be sure to avoid them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2006
    Posts
    39
    http://www.cs.umt.edu/CS/COURSES/CS3...20Playbook.htm

    this is the site
    but i have the sql file too.

    i have to practice this for my exam.
    my prof said it would be good to study to prep for the test.
    it won't be so hard like sql playbook. but it may be a good practice

    i never learned those crazy divide sql lines
    u can just scroll down all those queries that have the title "DIVIDE"


    That query i posted question 18
    it ask
    lists the items delivered by every supplier that delivers all items of type N
    Last edited by Chanyeehon; 12-06-07 at 00:15.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i'm not going to be able to help you

    how come you're in toronto taking a course in montana?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That query is horrid, and what the heck do you mean by DIVIDE?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    relational division:
    http://luhong.wordpress.com/2006/03/...vision-in-sql/

    personally, i think the double negative (if there are no courses that the student did not take, then he musta took 'em all) is needlessly—and hopelessly—complex
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It's in one of Celko's SQL books (not the smarties one). That was when I learned you can corrolate through nested sub queries. Oh happy days!

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's also in the Smarties book, sections 19.2, 19.3, pages 406-418 (latest edition) -- pilots, pilotskills, hangars -- and the examples all use COUNTs rather than nefarious doubly-nested negatives
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The hangers one is the one I meant. Is it in smarties? I must have missed that

    I prefer the count method coz I can get me noggin round it. There is defo a nested corrolated subquery solution in the other book - you have my word Rudy

  10. #10
    Join Date
    Oct 2006
    Posts
    39
    Quote Originally Posted by r937
    sorry, i'm not going to be able to help you

    how come you're in toronto taking a course in montana?
    i am in Toronto, but i guess that book came from there.

    that's fine. i know it's way more complex than average. Plus, i dont think you
    or I will be using those kinds of queries at work.

    but as i look into more of those queries i can see a pattern.

  11. #11
    Join Date
    Oct 2006
    Posts
    39
    Quote Originally Posted by r937
    relational division:
    http://luhong.wordpress.com/2006/03/...vision-in-sql/

    personally, i think the double negative (if there are no courses that the student did not take, then he musta took 'em all) is needlessly—and hopelessly—complex
    yea
    i think in other words... students that took all the courses gets canceled out, leaving only them in the result.
    it's still not totally clear in my head. but this is a simple way for me to make it more clear even if it's not really correct.
    Last edited by Chanyeehon; 12-06-07 at 15:46.

Posting Permissions

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