Query for Row count

I am trying to create a header file that lists the
table_name, row_count and date. The count for tables are
from the tables themselves (Basic row count)except 2
tables that have join statement. How can I include the 2
with the join statements with the rest of them ?
SELECT A.name AS name, B.rows AS [row count],
LEFT(GETDATE(), 12) AS date
FROM sysobjects A
JOIN sysindexes B ON A.id = B.id
WHERE A.type = 'U'AND A.name in
('table1','table2','table3','table4','table5','tab le6','tab
le7','table8')
AND B.indid < 2
ORDER BY A.name
--One of the table with the join
SELECT Count(*)
fromT9 table9
left join T10 table10
on table9.customer_id = table10.oid
T.I.A
If it's a left join, why not just use the count for table9 ?
http://www.aspfaq.com/
(Reverse address to reply.)
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:926601c496a5$921c8790$a301280a@.phx.gbl...
> I am trying to create a header file that lists the
> table_name, row_count and date. The count for tables are
> from the tables themselves (Basic row count)except 2
> tables that have join statement. How can I include the 2
> with the join statements with the rest of them ?
>
> SELECT A.name AS name, B.rows AS [row count],
> LEFT(GETDATE(), 12) AS date
> FROM sysobjects A
> JOIN sysindexes B ON A.id = B.id
> WHERE A.type = 'U'AND A.name in
> ('table1','table2','table3','table4','table5','tab le6','tab
> le7','table8')
> AND B.indid < 2
> ORDER BY A.name
>
> --One of the table with the join
> SELECT Count(*)
> from T9 table9
> left join T10 table10
> on table9.customer_id = table10.oid
> T.I.A
|||Because I am getting a different count 1034 in one and
1029 in the other one. Can the cause be something else ?
Thanks.

>--Original Message--
>If it's a left join, why not just use the count for
table9 ?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:926601c496a5$921c8790$a301280a@.phx.gbl...
('table1','table2','table3','table4','table5','tab le6','tab
>
>.
>
|||Well, the count from your left join is not going to be stored in sysobjects,
because your left join is not a table and doesn't have any indexes!
What you could do is create a view, and then do something like:
SELECT 'SELECT t = '''+TABLE_NAME+''', c = COUNT(*) FROM '+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN ('table1', ... , 'table8', 'view_table9_LJ_table10')
Run that, then copy the script from lower pane to top pane, and run it.
http://www.aspfaq.com/
(Reverse address to reply.)
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:91e101c496aa$06e898d0$a501280a@.phx.gbl...
> Because I am getting a different count 1034 in one and
> 1029 in the other one. Can the cause be something else ?

Query for Row count

I am trying to create a header file that lists the
table_name, row_count and date. The count for tables are
from the tables themselves (Basic row count)except 2
tables that have join statement. How can I include the 2
with the join statements with the rest of them '
SELECT A.name AS name, B.rows AS [row count],
LEFT(GETDATE(), 12) AS date
FROM sysobjects A
JOIN sysindexes B ON A.id = B.id
WHERE A.type = 'U'AND A.name in
('table1','table2','table3','table4','table5','table6','tab
le7','table8')
AND B.indid < 2
ORDER BY A.name
--One of the table with the join
SELECT Count(*)
from T9 table9
left join T10 table10
on table9.customer_id = table10.oid
T.I.AIf it's a left join, why not just use the count for table9 ?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:926601c496a5$921c8790$a301280a@.phx.gbl...
> I am trying to create a header file that lists the
> table_name, row_count and date. The count for tables are
> from the tables themselves (Basic row count)except 2
> tables that have join statement. How can I include the 2
> with the join statements with the rest of them '
>
> SELECT A.name AS name, B.rows AS [row count],
> LEFT(GETDATE(), 12) AS date
> FROM sysobjects A
> JOIN sysindexes B ON A.id = B.id
> WHERE A.type = 'U'AND A.name in
> ('table1','table2','table3','table4','table5','table6','tab
> le7','table8')
> AND B.indid < 2
> ORDER BY A.name
>
> --One of the table with the join
> SELECT Count(*)
> from T9 table9
> left join T10 table10
> on table9.customer_id = table10.oid
> T.I.A|||Because I am getting a different count 1034 in one and
1029 in the other one. Can the cause be something else '
Thanks.
>--Original Message--
>If it's a left join, why not just use the count for
table9 ?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:926601c496a5$921c8790$a301280a@.phx.gbl...
>> I am trying to create a header file that lists the
>> table_name, row_count and date. The count for tables are
>> from the tables themselves (Basic row count)except 2
>> tables that have join statement. How can I include the 2
>> with the join statements with the rest of them '
>>
>> SELECT A.name AS name, B.rows AS [row count],
>> LEFT(GETDATE(), 12) AS date
>> FROM sysobjects A
>> JOIN sysindexes B ON A.id = B.id
>> WHERE A.type = 'U'AND A.name in
('table1','table2','table3','table4','table5','table6','tab
>> le7','table8')
>> AND B.indid < 2
>> ORDER BY A.name
>>
>> --One of the table with the join
>> SELECT Count(*)
>> from T9 table9
>> left join T10 table10
>> on table9.customer_id = table10.oid
>> T.I.A
>
>.
>|||Well, the count from your left join is not going to be stored in sysobjects,
because your left join is not a table and doesn't have any indexes!
What you could do is create a view, and then do something like:
SELECT 'SELECT t = '''+TABLE_NAME+''', c = COUNT(*) FROM '+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN ('table1', ... , 'table8', 'view_table9_LJ_table10')
Run that, then copy the script from lower pane to top pane, and run it.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:91e101c496aa$06e898d0$a501280a@.phx.gbl...
> Because I am getting a different count 1034 in one and
> 1029 in the other one. Can the cause be something else '

Query for results without using Expr

I'm working with SQL Server 2005.

I am new to sql and am trying to learn it and understand how to use the queries.

I can query for basic information, no problem but I am trying something more

complex and am having trouble. I'm trying to return up to 10 records but every

time I test in my browser I only see the first record. I dont understand why I

can't see more then just that one record.

There are three tables. The names & descriptions and URL and Links are part of

the same table. They are called:

TableMain
TableNames
TableDescriptions
TableURL
TableLinks

So the names and descripts are in one table and URL and Links in another.

Here is the sql query code:

[CODE]
strSQL = "SELECT *, TableMain.ID AS Expr1, TableMain.Unit AS Expr2,
TableMain.Air_Date AS Expr3, TableNames.ID AS Expr4, Table.Names AS Expr5,
Table.Descriptions AS Expr6, TableLinks.URL AS Expr7 FROM TableMain CROSS JOIN
TableNames CROSS JOIN TableLinks WHERE (DATEDIFF(day, GETDATE(), Table.Air_Date) = 0) AND (Table.Air_Date <= GETDATE())"
[/CODE]

I was told that I dont really need to use Expr but every time I pull them out of

this code segment and re-paste into the query editor then click on Run, it puts

them back in and shows the correct result. It's working ok in the query editor so

why wont it show me more then just the first record inside of TableNames when

tested from a browser?

I was told the other day that it was because I wasn't looping through the

records. But I've tried using:

<%Do While Not RS.EOF%>

my html / asp display code in here....

<strong><% Response.Write RS ("TableName") %> </strong> <% Response.Write

RS("TableDescription") %><br /><br />
Related Links:<br />
<li class='basic'><A HREF="<%= RS("URL") %>"><%= RS("Description") %></A></li>

<%
rs.MoveNext
Loop
%>

Please any help with this would be great

First, let me respond to the issue of 'Expr#'. This is an 'Alias', an assigned name for a column that doesn't have a name. When you use [ SELECT * ], you are asking for all columns in the table, and then when you list the columns, as in [ TableMain.ID ], it produces another column with the same name. Two columns having the same name are not allowed, so an 'Alias' is assigned for one of them.

Listing the columns, and NOT using [ SELECT * ] is the preferred method.

Now about the use of CROSS JOINS. A CROSS JOIN produces every possible combination from the two tables in the JOIN. If one table has 1000 rows, and the other table has 1000 rows, a CROSS JOIN will produce 1,000,000 rows. And if you add a thrid table as a CROSS JOIN, and it has only 100 rows, the result is 100,000,000 rows. Are you sure that is your intention?

About displaying 10 records. Are you using a grid control? How are you displaying the records?

sql