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. Advertising

  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 MCSD, Microsoft Visual FoxPro MVP
    www.cindywinegarden.com


    "Kaimuri" <> wrote in message
    news:%...
    > ...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?....
    Cindy Winegarden, Dec 24, 2004
    #2
    1. Advertising

  3. >Imagine that when SQL joins tables it makes one long row out of the matching
    >rows from the two (or more) tables.


    .... 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.

    >Then for "Count(*)" it just counts the
    >rows. However, if you specify Count(Distinct Tablex.Isbn) then it counts
    >something more specific.


    .... 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!

    "The Poster Formerly Known as Kline Sphere" <.> wrote in message
    news:...
    > >Imagine that when SQL joins tables it makes one long row out of the
    > >matching
    >>rows from the two (or more) tables.

    >
    > ... 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.
    >
    >>Then for "Count(*)" it just counts the
    >>rows. However, if you specify Count(Distinct Tablex.Isbn) then it counts
    >>something more specific.

    >
    > ... and in the process, excludes non null values from the result.
    >
    > Kline Sphere (Chalk) MCNGP #3
    Kaimuri, Dec 29, 2004
    #4
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Joachim Krais
    Replies:
    2
    Views:
    14,843
    Andre Beck
    Nov 23, 2003
  2. John Ramsden
    Replies:
    0
    Views:
    812
    John Ramsden
    Jul 24, 2004
  3. zher
    Replies:
    2
    Views:
    8,854
  4. Replies:
    0
    Views:
    520
  5. eddiec
    Replies:
    1
    Views:
    388
    Andy Barkl
    Jan 31, 2005
Loading...

Share This Page