+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
Coding Thread, I hate SQL more... in Coding and Web Development; I've been deliberately avoiding SQL most of my life so this feels a teensy bit difficult, but I bet it ...
  1. #1

    Join Date
    Jan 2006
    Location
    Surburbia
    Posts
    2,178
    Thank Post
    74
    Thanked 307 Times in 243 Posts
    Rep Power
    115

    I hate SQL more...

    I've been deliberately avoiding SQL most of my life so this feels a teensy bit difficult, but I bet it isn't really and one of you will prove that in no time at all

    Take two tables:

    1) Authors (authorname, vital statistics etc.)
    2) Books (title, authorname, ISBN etc.)

    How do I select a list of authors (authornames) who didn't write a book titled "Zen and the Art of Educational IT"?

  2. #2

    webman's Avatar
    Join Date
    Nov 2005
    Location
    North East England
    Posts
    8,401
    Thank Post
    637
    Thanked 961 Times in 661 Posts
    Blog Entries
    2
    Rep Power
    319
    Probably something like...

    Code:
    SELECT DISTINCT(authors.authorname) FROM authors, books
    WHERE authors.authorname = books.authorname
    AND books.title != "Zen and the Art of Educational IT"

  3. #3

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    17,617
    Thank Post
    514
    Thanked 2,442 Times in 1,890 Posts
    Blog Entries
    24
    Rep Power
    831
    Pah! beat me to it. Had just nearly typed the exact same thing!!! (Except I'd have used NOT instead of a != (ie NOT books.title = "Zen ...").

  4. #4

    webman's Avatar
    Join Date
    Nov 2005
    Location
    North East England
    Posts
    8,401
    Thank Post
    637
    Thanked 961 Times in 661 Posts
    Blog Entries
    2
    Rep Power
    319
    Hehe, must be right then

  5. #5

    Michael's Avatar
    Join Date
    Dec 2005
    Location
    Birmingham
    Posts
    9,262
    Thank Post
    242
    Thanked 1,568 Times in 1,250 Posts
    Rep Power
    340
    The way I look at SQL is like Excel on steroids! SQL is very good when it works

  6. #6

    webman's Avatar
    Join Date
    Nov 2005
    Location
    North East England
    Posts
    8,401
    Thank Post
    637
    Thanked 961 Times in 661 Posts
    Blog Entries
    2
    Rep Power
    319
    Lol - that's one way of looking at it

    It is good... albeit confusing at times. Take Oracle, for example, and the method used to insert multiple rows in one statement:

    Code:
    INSERT ALL
      INTO table VALUES (1, 'foo', 'bar')
      INTO table VALUES (2', 'abc', 'def')
    SELECT * FROM dual;
    You select from a magical built-in table called 'dual'.

  7. #7

    Join Date
    Jan 2006
    Location
    Surburbia
    Posts
    2,178
    Thank Post
    74
    Thanked 307 Times in 243 Posts
    Rep Power
    115
    Probably something like...
    Mmm.. probably something like that only much more complicated

    That didn't work for me... returns the same number of rows with or without the "AND books.title.." line.

  8. #8

    webman's Avatar
    Join Date
    Nov 2005
    Location
    North East England
    Posts
    8,401
    Thank Post
    637
    Thanked 961 Times in 661 Posts
    Blog Entries
    2
    Rep Power
    319
    What SQL is this - MySQL, MS, Postgres? What is the exact relationship between the two tables?

  9. #9

    Join Date
    Jan 2006
    Location
    Surburbia
    Posts
    2,178
    Thank Post
    74
    Thanked 307 Times in 243 Posts
    Rep Power
    115
    MS & purely platonic.

    The trouble with that query.. I think.. is that it returns authors who have written some books that aren't titled "Zen..". That's not the same as authors who have not written one titled "Zen..".

    This sub-query approach seems to work-maybe-but-being-SQL-naive-I'm-not-sure:

    SELECT DISTINCT(authorname)
    FROM Books
    WHERE authorname NOT IN (SELECT DISTINCT(authorname) FROM Books WHERE title = 'Zen and the Art of Educational IT')

    Note that only uses the Books table and won't tell me about authors who haven't written a single book, but in that case they're not proper authors so I don't care!

  10. #10

    Join Date
    Jul 2009
    Location
    Shropshire
    Posts
    49
    Thank Post
    2
    Thanked 4 Times in 3 Posts
    Rep Power
    11
    I'm doing oracle and SQL at college, I'll dig out the oracle manual for you if you havnt cracked it soon, thin the suggestions are right might need to tweak the syntax slightly for your version of SQL.

    I never knew until todays lecture that you could assign variables in SQL and other such cool stuff.

  11. #11

    Join Date
    Oct 2007
    Location
    Lincolnshire
    Posts
    133
    Thank Post
    0
    Thanked 22 Times in 22 Posts
    Rep Power
    17
    To include authors who haven't written any books you'll need to use an outer join. Something like this maybe (I don't know mssql):

    Code:
    SELECT DISTINCT(authors.authorname) 
    FROM authors LEFT OUTER JOIN books 
    ON authors.authorname = books.authorname
    AND books.title != "Zen and the Art of Educational IT"

  12. #12

    Join Date
    Apr 2006
    Posts
    388
    Thank Post
    23
    Thanked 95 Times in 61 Posts
    Rep Power
    44
    Hello

    Using the author's name as a key between the two tables will cause you problems if you ever get books by two authors with the same name. Generally you'd use a numeric ID.

    For example:

    Create table authors(
    ID int,
    name varchar(60))
    GO

    create table books(
    ID int,
    author int,
    title varchar(200))
    GO

    insert into authors (ID, name) values (1, 'Neal Stephenson')
    insert into authors (ID, name) values (2, 'Robert Pirsig')
    insert into authors (ID, name) values (3, 'Mick Wall')
    insert into authors (ID, name) values (4, 'Mark Minasi')
    insert into authors (ID, name) values (5, 'Somone I made up')
    insert into authors (ID, name) values (6, 'Hopeless Failure of an Author')
    GO

    insert into books (ID, author, title) values (1, 1, 'Snow Crash')
    insert into books (ID, author, title) values (2, 1, 'Cryptonomicon')
    insert into books (ID, author, title) values (3, 2,'Zen and the Art of Motorcycle Maintenance')
    insert into books (ID, author, title) values (4, 3, 'Market Square Heroes')
    insert into books (ID, author, title) values (5, 4, 'Loads of cool books about Windows')
    insert into books (ID, author, title) values (6, 5, 'Zen and the art of educational IT')
    insert into books (ID, author, title) values (7, 2,'Lila')
    insert into books (ID, author, title) values (8, 5, 'I like beans')
    GO


    In order to do your query you'd need to do the NOT IN as you suggest, but using both tables:

    select distinct(name) from authors where ID not in
    (select author from books where title = 'Zen and the art of educational IT')


    This has the bonus of returning the author whose books aren't in the books table.

  13. Thanks to theriver from:

    PiqueABoo (7th October 2009)

  14. #13

    Join Date
    Jan 2006
    Location
    Surburbia
    Posts
    2,178
    Thank Post
    74
    Thanked 307 Times in 243 Posts
    Rep Power
    115
    This has the bonus of returning the author whose books aren't in the books table.
    I'm absolutely certain that every "authorname" will be unique and each one will have written lots of "books" because these are just easier to explain substitutes for the real tables..

    ..but I guess I'm a bit of a perfectionist because book-less authors was still nagging me this morning... better to do it right first time than have to go back and fix it later.. and it's something I can easily imagine wanting to do in other scenarios.

  15. #14

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    10,701
    Thank Post
    1,783
    Thanked 2,168 Times in 1,603 Posts
    Rep Power
    769
    Quote Originally Posted by PiqueABoo View Post
    I'm absolutely certain that every "authorname" will be unique .
    Oh no they won't.. I'm also the school Librarian here!

    the only thing unique about a book is its ISBN - unless you have multiple copies!

  16. #15

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,154
    Thank Post
    114
    Thanked 527 Times in 450 Posts
    Blog Entries
    2
    Rep Power
    123
    Quote Originally Posted by elsiegee40 View Post
    Oh no they won't.. I'm also the school Librarian here!

    the only thing unique about a book is its ISBN - unless you have multiple copies!
    And that's why you have an accession number :-)

    (I taught SQL to librarians ...)

SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. Windows 7 - HATE IT, HATE IT, HATE IT
    By mattx in forum Windows 7
    Replies: 63
    Last Post: 2nd October 2009, 10:49 AM
  2. The jobs you just hate.
    By FN-GM in forum General Chat
    Replies: 66
    Last Post: 23rd April 2009, 12:25 PM
  3. MS SQL 2005 - Dump SQL
    By tom_newton in forum Windows
    Replies: 7
    Last Post: 23rd January 2008, 05:10 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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