Query to return single result from multiple 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

SQL Server 2008 Forums

Transact-SQL (2008)

Query to return single result from multiple tables

Author

Topic

bhavtosh
Starting Member

4 Posts

Posted - 2012-03-02 : 06:35:33

Hi,

i have below tables with below fields:

TableWO
CreatedBY UpdatedBY
user1 user2
user1 user1
user2 user3

TableData
UpdatedBy DataType[if =1 means Event else Data]
user1 1
user3 2
user3 2
user2 1
user1 1

TableUser
UserID Fisrtname
Lastname
user1 Bill Luther
user2 Charlie Martin
user3 Dean John

Now i wish to have a select query that should display the below columns:

UserID - from TableUser

Fullname - from TableUser

Count of CreatedBy - from TableWO

Count of UpdatedBy - from TableWO

Count of EventUpdated - - from TableData

Count of DataUpdated - - from TableData

any help is really appreciated

thanks,
bhavtosh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-02 : 12:39:37

[code]
SELECT u.UserID,u.Fisrtname + ' ' + u.Lastname AS FullName,
SUM(w.createdCount) AS createdCount,
SUM(w.updatedCount) AS updatedCount,
e.EventCnt,
e.dataCnt
FROM TableUser u
INNER JOIN (SELECT CreatedBY AS UserID,COUNT(*) AS createdCount,CAST(0 AS int) AS updatedCount
FROM TableWO
UNION ALL
SELECT UpdatedBy AS UserID,0 ,COUNT(*)
FROM TableWO
)w
ON w.UserID = u.UserID
INNER JOIN (SELECT UpdatedBy,
COUNT(CASE WHEN DatatYpe=1 THEN 1 ELSE NULL END) AS EventCnt,
COUNT(CASE WHEN DatatYpe=2 THEN 1 ELSE NULL END) AS dataCnt,
FROM tableData
GROUP BY UpdatedBy
)e
ON e.UpdatedBy = u.UserID
GROUP BY u.UserID,u.Fisrtname + ' ' + u.Lastname,e.EventCnt,
e.dataCnt
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bhavtosh
Starting Member

4 Posts

Posted - 2012-03-04 : 01:47:50

thanks buddy :)

thanks,
bhavtosh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-04 : 14:28:01

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources