Selecting from 2 tables when there is no item in the second table

[BenRussell]

I have 2 tables (for instance):

BOOKS
----------------------------------
book_id An ID number
author
title

BOOK_SIZE
----------------------------------
book_id The ID number from BOOKS
size i.e. Large, Small, etc.

The problem is that say I have 2 books in the BOOKS table. Book #1 has two sizes (Large and Small), but Book #2 has only one size (Large). So I only have 2 entries in BOOK_SIZE for Book #1, and do NOT have an entry in BOOK_SIZE for Book #2.

The problem occurs when I try the following SQL statement:

SELECT books.book_id, books.author, books.title, book_size.size FROM books, book_size WHERE (books.book_id = book_size.book_id);

This of course returns 2 results: Both for Book #1 and NONE for Book #2.

How can I get around this so that it will still give me results for Book #1, but will just leave the 'book_sizes.size' field empty?

- Ben

 

[hos2]

SELECT books.book_id, books.author, books.title, book_size.size FROM books LEFT OUTER JOIN book_size ON books.book_id = book_size.book_id;

perhaps. but strange since you say there is one value for book 2 the query should return 3 results instead of 2

 

[BenRussell]

But it only returns 2 since the BOOK_SIZE table only has 2 entries (both of them for Book #1 however). Since it finds no entries in BOOK_SIZE for Book #2, it returns no results.

- Ben

 

[hos2]

so my query should do the trick I think. but as you say it in your question I understood it like

booktable
book1
book2

booksize
book1,large
book1,small
book2,large

 

[BenRussell]

Oh, maybe I didnt explain well enough
I meant

booktable
book1
book2

book_size
book1,large
book1,small

And that was it (just 2 entries in each table)

- Ben

 

[sleipnir214]

And what you want from the query is:

book1 large
book1 small
book2 NULL
book2 NULL

Correct?


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[BenRussell]

Yes, that is correct. I would prefer no LEFT OUTER JOIN statements because I dont really understand what they are, but if is required, then thats okay too.

- Ben

 

[sleipnir214]

Well, LEFT JOIN is the only way to show combinations that aren't there.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[hos2]

left outer join means "show all rows from the table on the left and where available the rows from the table on the right"

so the left outer join is the only way

FROM books LEFT OUTER JOIN book_size ON books.book_id = book_size.book_id;

show all the rows from books and where available the rows from the book_size