I hate SQL more...

[PiqueABoo]

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"?

[webman]

Probably something like...

 

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

[localzuk]

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 ...").

[webman]

Hehe, must be right then

[Michael]

The way I look at SQL is like Excel on steroids! SQL is very good when it works

[webman]

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:

 

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'.

[PiqueABoo]

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.

[webman]

What SQL is this - MySQL, MS, Postgres? What is the exact relationship between the two tables?

[PiqueABoo]

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!

[whitenight639]

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.

[keithu]

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):

 

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"

[theriver]

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.

[PiqueABoo]

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.

[elsiegee40]

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!

[srochford]

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 ...)

[INeedAUsername]

..try doing a backup on a shared storage - thats where the fun begins

[PiqueABoo]

the only thing unique about a book is its ISBN

 

Hah! Try telling that to someone whose first real job wasn't working on a [county|city|small country] size public library system. That is the principle though. ;b

 

Never again. I'm doing something else and used authors+books as a convenient way to express a generic problem.