Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Computer Certification > MCSD > Count(*) in a Subquery with multiple tables: How does SQL determine which table to generate the Count() from?

Reply
Thread Tools

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

 
 
Kaimuri
Guest
Posts: n/a
 
      12-23-2004
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


 
Reply With Quote
 
 
 
 
Cindy Winegarden
Guest
Posts: n/a
 
      12-24-2004
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
http://www.velocityreviews.com/forums/(E-Mail Removed) www.cindywinegarden.com


"Kaimuri" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> ...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?....



 
Reply With Quote
 
 
 
 
The Poster Formerly Known as Kline Sphere
Guest
Posts: n/a
 
      12-27-2004
>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
 
Reply With Quote
 
Kaimuri
Guest
Posts: n/a
 
      12-29-2004
Thanks a bunch..Cindy and Kline!

"The Poster Formerly Known as Kline Sphere" <.> wrote in message
news:(E-Mail Removed)...
> >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



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Failed to generate a user instance of SQL Server. Only an integratedconnection can generate a user instance. Harlan Messinger ASP .Net 2 03-28-2010 06:51 PM
System.Data.SqlClient.SqlException: Subquery returned more than 1 value. Rod ASP .Net 6 12-22-2009 05:12 PM
ReadOnlyException while trying to update a subquery column using TableAdapter Bogdan ASP .Net 3 04-16-2008 07:57 PM
XQuery equivalent to NOT IN (subquery) thecolour XML 2 06-26-2007 01:13 PM
SQL Subquery and alias for datagrid use .Net Sports ASP .Net 2 07-07-2005 02:34 AM



Advertisments