SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,101
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Join and null values??

    Hi all, hope someone can help with this.
    I have a join statement.
    Code:
    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
    What I lack in acuracy I make up for in misteaks

  2. #2
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,101
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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
    What I lack in acuracy I make up for in misteaks

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you're thinking of LEFT OUTER JOIN

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

    that would also make your queries simpler
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    re: your original query...
    Quote Originally Posted by lorenw View Post
    This works fine if Loren had a book about the outdoors and cooking.
    but ~only~ if there's exactly one of each

    more than that, and you get cross join effects
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,101
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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?
    What I lack in acuracy I make up for in misteaks

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by lorenw View Post
    Its a pure hypothetical query, as in there are no books.
    in that case, my answer is purely hypothetical, too

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

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