Get Count from two unrelated tables

[darbid]

I am trying to get get the number of records from 2 tables in the one SQL query.

Access JET does not even have an error for this.

SELECT (
SELECT COUNT(*)
FROM tbl_task
) AS count1,
(
SELECT COUNT(*)
FROM  tbl_note
) AS count2;

Thanks in advance.

 

[jdraw]

I think this syntax will work. Good luck

SELECT Sum(x) AS MyTotal
FROM (SELECT count(*) as x
FROM TBL_TASK
union select count(*) as x from TBL_NOTE)

 

[darbid]

certainly does work, thank you.

But What I am looking for is to get the count for each table, not the total count for both tables.

 

[jdraw]

certainly does work, thank you.

But What I am looking for is to get the count for each table, not the total count for both tables.

Try this

SELECT x AS MyTotal
FROM (SELECT count(*) as x
FROM TBL_TASK
union select count(*) as x from TBL_NOTE)

OR (depending on what you want to see)

SELECT * 
FROM (SELECT "firstTbl cnt",count(*) as x
FROM Tbl_task
union select "secondTbl cnt",count(*) as x from Tbl_Note)

 

[darbid]

Thank you jdraw,

I was wanting 1 row with each count and not 2 rows but I suppose if I want something special I should go learn it myself

Thank you again.

 

[gemma-the-husky]

once you have a stored query with 2 rows (or more) - you can just do a dsum of this query to get thev final result.

nothing has to be done in a single query!

 

[darbid]

Actually I am playing with Visual Studio 2010 and ADO.NET.

I am making a little update tool and needed to get some info from my database.

I now must say my question was stupid because....

I still use Access to work out my SQL queries. My original query was not working with JET thus my reason for the question.

But as I am using ADO and querying SQL Server 2000 it does not matter what JET accepts. As it turns out my original query works fine.

Thus I am sorry for the question.