SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    Location
    doberman
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Easy SQL Statement, Need a hand with.

    Hey SP, I thought this would be easier than it is. But Ineed help with this:

    Assume you have two tables in an Oracle database, one named BOOK and one named AUTHOR. There is a column in BOOK named author_id that references the AUTHOR table. The idea here is that a single author may have written multiple books. How would I write a SQL query that produces a result similar to this:



    Author Name Total number of books written

    ----------- -----------------------------

    Charles Dickens 23

    Dr. Seuss 51

    Norman Mailer 20

    Harper Lee 1



    No one table contains all the information necessary to produce this
    report.
    Got logical business solutions? Get SuM!
    Greg Starr (SuM)
    LogicStation.net
    Florida Based Contract Web Developer

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select author, count(books.author_id) as books_written
      from authors
    left outer 
      join books
        on authors.id = books.author_id
    group
        by author
    your design does not allow for a book written by two authors
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2005
    Location
    doberman
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Whoa, ok this looks good will try it. But can you run this down for me. I don't understand what this is doing.

    Thanks
    Got logical business solutions? Get SuM!
    Greg Starr (SuM)
    LogicStation.net
    Florida Based Contract Web Developer

  4. #4
    SitePoint Zealot CdeMky's Avatar
    Join Date
    Sep 2004
    Location
    United States
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select author, count(books.author_id) as books_written
    from authors
    left outer
    join books
    on authors.id = books.author_id
    group
    by author


    Selects the author, numer of books written by the author using a left outer join.

    Here is a definition of a left outer join:

    The left outer join retrieves records from both tables, retrieving all the records from the left table and any records from the right table where the condition values match. If there are no matching values in from the right table, the join still retrieves all the records from the left table. Any columns from the right table that are unmatchedare left NULL. Consequently, the resulting recordset often appears to have incomplete records.
    So I suppose you could end up with results like this if there aren't any book entries by an author in the books table:

    Charles Dickens 23

    Dr. Seuss NULL

    Norman Mailer NULL

    Harper Lee 1

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    almost correct

    Charles Dickens 23
    Dr. Seuss 0
    Norman Mailer 0
    Harper Lee 1

    COUNT(columnname) will ignore NULL and return 0 if there were no matches
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •