Count(*) in a Subquery with multiple tables: How does SQL determine which table to generate the Coun

Discussion in 'MCSD' started by Kaimuri, Dec 23, 2004.

  1. Kaimuri

    Kaimuri Guest

    I am doing an exercise on subqueries from the Microsoft course # 2071.
    There are 3 tables that I need to query in order to return 4 columns:
    title_no, title, isbn and Total Reserved. The Total Reserved column is the
    per-isbn(book) count of books on reserve with more than 50 reservations and
    less than 5 copies of the book.

    The tables are as follows
    Reservation Title Loan
    --------------- ------ --------
    isbn title_no isbn
    member_no title copy_no
    log_date author title_no
    log_date synopsis member_no
    out_date
    due_date

    Here's the solution. My question is: How does SQL determing which table's
    rows to count?

    Select t.title_no, title, l.isbn, count(*) as 'Total Reserved'--how does sql
    know which table to count from?
    from title AS t
    inner join loan AS l
    ON t.title_no=l.title_no
    inner join reservation r
    ON l.isbn=r.isbn
    WHERE r.isbn IN
    (select isbn
    from reservation
    group by isbn
    having Count(*)>50)
    And l.copy_no<5
    Group By t.title_no, title, l.isbn
     
    Kaimuri, Dec 23, 2004
    #1
    1. Advertisements

  2. Hi Kaimuri,

    Imagine that when SQL joins tables it makes one long row out of the matching
    rows from the two (or more) tables. Then for "Count(*)" it just counts the
    rows. However, if you specify Count(Distinct Tablex.Isbn) then it counts
    something more specific.
     
    Cindy Winegarden, Dec 24, 2004
    #2
    1. Advertisements

  3. Imagine that when SQL joins tables it makes one long row out of the matching
    .... To clarify, where 'tables' mean the resulting tuples from one or
    more query parts in the statement.

    Too often the term 'table' is used to describe a relation, which is
    incorrect.
    .... and in the process, excludes non null values from the result.

    Kline Sphere (Chalk) MCNGP #3
     
    The Poster Formerly Known as Kline Sphere, Dec 27, 2004
    #3
  4. Kaimuri

    Kaimuri Guest

    Thanks a bunch..Cindy and Kline!

     
    Kaimuri, Dec 29, 2004
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.