Creating a View by using multi tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating a View by using multi tables

Author

Topic

bashu
Starting Member

6 Posts

Posted - 2007-06-12 : 03:04:59

Here I need to create a view by using following criteria, there is 3 tables which are Tbl.adminCategory, tbl_adminuser, tbl_outbox respectively. I am working on 2000SQL server

I am treing to create view as following but getting some error.

SELECT tbl_adminuser.adminUserName, tbl_AdminCategory.Name,
COUNT(tbl_outbox.msgUserID),
FROM tbl_adminuser INNER JOIN
tbl_AdminCategory ON
tbl_adminuser.adminUserID = tbl_AdminCategory.CatID INNER JOIN
tbl_outbox ON tbl_AdminCategory.CatID = tbl_outbox.msgUserID
AND tbl_outbox.msgUserID <> 0
GROUP BY tbl_outbox.msgUserID
But I am getting error pls correct the view,

thanks to all

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 03:07:49

you will need to named column


SELECT tbl_adminuser.adminUserName, tbl_AdminCategory.Name,
COUNT(tbl_outbox.msgUserID) as cnt,
FROM tbl_adminuser INNER JOIN
tbl_AdminCategory ON
tbl_adminuser.adminUserID = tbl_AdminCategory.CatID INNER JOIN
tbl_outbox ON tbl_AdminCategory.CatID = tbl_outbox.msgUserID
AND tbl_outbox.msgUserID <> 0
GROUP BY tbl_outbox.msgUserID tbl_adminuser.adminUserName, tbl_AdminCategory.Name

bashu
Starting Member

6 Posts

Posted - 2007-06-12 : 03:42:46

Hi KH sir Thank you very much. its working correctly but I need to add date also from tbl_outbox y caz search criteria should be based on the tbl_outbox.msgDate when I am adding tbl_outbox.msgDate then its showing whole records. I need to display Just Count of records on report file in .Net 2.0. only

quote:

Originally posted by khtan

you will need to named column


SELECT tbl_adminuser.adminUserName, tbl_AdminCategory.Name,
COUNT(tbl_outbox.msgUserID) as cnt,
FROM tbl_adminuser INNER JOIN
tbl_AdminCategory ON
tbl_adminuser.adminUserID = tbl_AdminCategory.CatID INNER JOIN
tbl_outbox ON tbl_AdminCategory.CatID = tbl_outbox.msgUserID
AND tbl_outbox.msgUserID <> 0
GROUP BY tbl_outbox.msgUserID tbl_adminuser.adminUserName, tbl_AdminCategory.Name

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 05:07:48

quote:
I need to add date also from tbl_outbox y caz search criteria should be based on the tbl_outbox.msgDate when I am adding tbl_outbox.msgDate then its showing whole records.

Sorry don't quite get what you want. Can you post some sample data and the result that you want ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-12 : 08:46:36

Note that date column also includes Time as well. You need to omit it when using in the Group by clause
Also Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources