Join and null values?

Databases
1

Hi all, hope someone can help with this.
I have a join statement.


SELECT booksoutdoors.title
, booksoutdoors.author
, bookscooking.title
, bookscooking.author

FROM booksoutdoors, bookscooking 
WHERE bookscooking.author = 'Loren'
AND booksoutdoors.author = 'Loren'

This works fine if Loren had a book about the outdoors and cooking.
I am trying to get this to work if the author only had a cookbook.

I’m thinking of a CASE situation but since the author is not in books.cooking I don’t get a NULL so this is where I am totally lost.

Any advice or help is greatly appreciated.
Thanks
Loren

2

Working on this a little more and the conclusion is to use conventional JOINs.

Just one more question, does the above query add any extra overhead compared to conventional JOINs? lets say an author would always have an outdoors book and a cooking book, would it be good to use the above query?

Thanks

3

you’re thinking of LEFT OUTER JOIN :slight_smile:

by the way, why two separate books tables? why not one?

that would also make your queries simpler :wink:

4

re: your original query…

but ~only~ if there’s exactly one of each

more than that, and you get cross join effects

5

Its a pure hypothetical query, as in there are no books. Just playing around, I have your book but my joins keep giving me what I don’t want, That query gives me accurate results but only if the author is in both tables.
What kind of join does this FROM booksoutdoors, bookscooking do?

6

in that case, my answer is purely hypothetical, too

:slight_smile:

7

outdoors and cooking are possible values for a book category field, so one table for book and another for book-categories, and this allows a book to be both for outdoors and cooking.

book-categories {isdn, category}
book {isdn, some other fields,}

I’ll leave you to work out the authors bit…
but it’s not in the book table.