Query to return single result from multiple tables

Question

Post reply

Query to return single result from multiple tables

bhavtoshsharma

SSC Rookie

Points: 27
More actions
March 2, 2012 at 4:34 am

#251871

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
Good day ,Bhavtosh

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Zarko Jovanovic Right there with Babe Points: 732 More actions · Answer 1

plase read this:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 2

You need to post DDL, sample data and expected results for a tested answer.

Here's a best guess

SELECT u.UserID,

u.Firstname,

COUNT(wcb.CreatedBY) AS CountCreatedBY,

COUNT(wub.UpdatedBY) AS CountUpdatedBY,

COUNT(CASE WHEN d.DataType=1 THEN d.DataType END) AS EventUpdated,

COUNT(CASE WHEN d.DataType<>1 THEN d.DataType END) AS EventDataUpdated

FROM TableUser u

LEFT OUTER JOIN TableWO wcb ON wcb.CreatedBY=u.UserID

LEFT OUTER JOIN TableWO wub ON wub.UpdatedBY=u.UserID

LEFT OUTER JOIN TableData d ON d.UpdatedBy=u.UserID

GROUP BY u.UserID,u.Firstname

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

Not going to comment on the table design, as I'm sure others will mention that it's not exactly "best".

BEGIN TRAN

SELECT CreatedBY, UpdatedBY

INTO TableWO

FROM (VALUES('user1', 'user2'),('user1', 'user1'),('user2', 'user3'))a(CreatedBY, UpdatedBY)

SELECT UpdatedBy, DataType --[if =1 means Event else Data]

INTO TableData

FROM (VALUES('user1', 1),('user3', 2),('user3', 2),('user2', 1),('user1', 1))a(UpdatedBy, DataType)

SELECT UserID, Fisrtname, Lastname

INTO TableUser

FROM (VALUES('user1', 'Bill', 'Luther'),('user2', 'Charlie', 'Martin'),('user3', 'Dean', 'John'))a(UserID, Fisrtname, Lastname)

--Solution

SELECT UserID, COALESCE(Fisrtname + SPACE(1),'') + COALESCE(Lastname,'') AS Fullname,

countCreated AS [Count of CreatedBy],

countUpdated AS [Count of UpdatedBy],

Ent AS [Count of EventUpdated],

Dta AS [Count of DataUpdated]

FROM TableUser us

LEFT OUTER JOIN (SELECT CreatedBY, COUNT(CreatedBY)

FROM TableWO

GROUP BY CreatedBY) cr(CreatedBY, countCreated) ON us.UserID = cr.CreatedBY

LEFT OUTER JOIN (SELECT UpdatedBY, COUNT(UpdatedBY)

FROM TableWO

GROUP BY UpdatedBY) up(UpdatedBY, countUpdated) ON us.UserID = up.UpdatedBY

LEFT OUTER JOIN (SELECT UpdatedBy,

SUM(CASE WHEN DataType = 1 THEN 1 ELSE 0 END),

SUM(CASE WHEN DataType = 1 THEN 0 ELSE 1 END)

FROM TableData

GROUP BY UpdatedBy) updt(UpdatedBy, Ent, Dta) ON us.UserID = updt.UpdatedBy

ROLLBACK

Returns:

UserID Fullname Count of CreatedBy Count of UpdatedBy Count of EventUpdated Count of DataUpdated

------ -------------- ------------------ ------------------ --------------------- --------------------

user1 Bill Luther 2 1 2 0

user2 Charlie Martin 1 1 1 0

user3 Dean John NULL 1 0 2

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

As everyone else I would suggest to read through forum etiquette article by Jeff Moden, link also can be found at the bottom of my signature.

Here I try to show how your case should be present to forum:

-- sample table and data setup:

declare @TableWO table (CreatedBY varchar(20), UpdatedBY varchar(20))

insert @TableWO

values ('user1','user2'),

('user1','user1'),

('user2','user3')

declare @TableData table (UpdatedBy varchar(20), DataType tinyint)

insert @TableData

values ('user1',1),

('user3',2),

('user2',1),

('user1',1)

declare @TableUser table (UserID varchar(20), Firstname varchar(50), Lastname varchar(50))

insert @TableUser

values ('user1','Bill','Luther'),

('user2','Charlie','Martin'),

('user3','Dean','John')

People can easely cut-&-paste this code to their SSMS and re-create the case setup.

Actully, it also will be much clear if you would not only include the definition of business requirements but also the exact results you expect based on the data you gave. It would help your helpers to validate their solution against your reqs, something like that:

I want results as per:

UserId,FullName,CountCreatedBY,CountUpdatedBY,CountEventUpdated,CountDataUpdated

user1,Bill Luther,2,1,2,0

user2,Charlie Martin,1,1,1,0

user3,Dean John,0,1,0,2

I just have a lot of free time today morning, that why I've proved the above. Usually you would have from me only the link to etiquette article ... 😀

And finally the soultion, using CTE's for a bit clear layout (together with setup, so you can run it straight a way:

declare @TableWO table (CreatedBY varchar(20), UpdatedBY varchar(20))

insert @TableWO

values ('user1','user2'),

('user1','user1'),

('user2','user3')

declare @TableData table (UpdatedBy varchar(20), DataType tinyint)

insert @TableData

values ('user1',1),

('user3',2),

('user2',1),

('user1',1)

declare @TableUser table (UserID varchar(20), Firstname varchar(50), Lastname varchar(50))

insert @TableUser

values ('user1','Bill','Luther'),

('user2','Charlie','Martin'),

('user3','Dean','John')

;with cte_CountOfCreatedBy

as

(

select CreatedBY, COUNT(*) as CountCreatedBY

from @TableWO

group by CreatedBY

)

, cte_CountOfUpdatedBy

as

(

select UpdatedBy, COUNT(*) as CountUpdatedBy

from @TableWO

group by UpdatedBy

)

, cte_CountOfEventData

as

( select UpdatedBy

,SUM(CASE WHEN isnull(DataType,0) = 1 THEN 1 ELSE 0 END) as CountEventUpdated

,SUM(CASE WHEN isnull(DataType,0) != 1 THEN 1 ELSE 0 END) as CountDataUpdated

from @TableData

group by UpdatedBy

)

select u.UserId

,u.FirstName + ' ' + u.LastName as FullName

,ISNULL(cc.CountCreatedBY, 0) as CountCreatedBY

,ISNULL(cu.CountUpdatedBY, 0) as CountUpdatedBY

,ISNULL(ed.CountEventUpdated, 0) as CountEventUpdated

,ISNULL(ed.CountDataUpdated, 0) as CountDataUpdated

from @TableUser as u

left join cte_CountOfCreatedBy as cc

on cc.CreatedBY = u.UserId

left join cte_CountOfUpdatedBy as cu

on cu.UpdatedBY = u.UserId

left join cte_CountOfEventData as ed

on ed.UpdatedBy = u.UserId

It also worth to mention why the solution by Mark-101232 will not pruduce right counts.

As soon as it can find more than one row per user in any of the tables he LEFT JOINs to, it mulitplies number of row occurances (eg. for all other LEFT JOIN's), you can see it easely by removing counts and grouping from his query:

SELECT u.UserID,

u.Firstname,

--COUNT(wcb.CreatedBY) AS CountCreatedBY,

--COUNT(wub.UpdatedBY) AS CountUpdatedBY,

--COUNT(CASE WHEN d.DataType=1 THEN d.DataType END) AS EventUpdated,

--COUNT(CASE WHEN d.DataType<>1 THEN d.DataType END) AS EventDataUpdated

wcb.CreatedBY,

wub.UpdatedBY,

d.DataType

FROM @TableUser u

LEFT OUTER JOIN @TableWO wcb ON wcb.CreatedBY=u.UserID

LEFT OUTER JOIN @TableWO wub ON wub.UpdatedBY=u.UserID

LEFT OUTER JOIN @TableData d ON d.UpdatedBy=u.UserID

--GROUP BY u.UserID,u.Firstname

You can see that User1 will be found 4 times in wcb.CreatedBY and in wcb.UpdatedBY, when it should be only 2 and 1 respectively.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

Eugene Elutin (3/2/2012)
As everyone else I would suggest to read through forum etiquette article by Jeff Moden, link also can be found at the bottom of my signature.
Here I try to show how your case should be present to forum:
-- sample table and data setup:
declare @TableWO table (CreatedBY varchar(20), UpdatedBY varchar(20))
insert @TableWO
values ('user1','user2'),
('user1','user1'),
('user2','user3')
declare @TableData table (UpdatedBy varchar(20), DataType tinyint)
insert @TableData
values ('user1',1),
('user3',2),
('user3',2),
('user2',1),
('user1',1)
declare @TableUser table (UserID varchar(20), Firstname varchar(50), Lastname varchar(50))
insert @TableUser
values ('user1','Bill','Luther'),
('user2','Charlie','Martin'),
('user3','Dean','John')
People can easely cut-&-paste this code to their SSMS and re-create the case setup.
Actully, it also will be much clear if you would not only include the definition of business requirements but also the exact results you expect based on the data you gave. It would help your helpers to validate their solution against your reqs, something like that:
I want results as per:
UserId,FullName,CountCreatedBY,CountUpdatedBY,CountEventUpdated,CountDataUpdated
user1,Bill Luther,2,1,2,0
user2,Charlie Martin,1,1,1,0
user3,Dean John,0,1,0,2
I just have a lot of free time today morning, that why I've proved the above. Usually you would have from me only the link to etiquette article ... 😀
And finally the soultion, using CTE's for a bit clear layout (together with setup, so you can run it straight a way:
declare @TableWO table (CreatedBY varchar(20), UpdatedBY varchar(20))
insert @TableWO
values ('user1','user2'),
('user1','user1'),
('user2','user3')
declare @TableData table (UpdatedBy varchar(20), DataType tinyint)
insert @TableData
values ('user1',1),
('user3',2),
('user3',2),
('user2',1),
('user1',1)
declare @TableUser table (UserID varchar(20), Firstname varchar(50), Lastname varchar(50))
insert @TableUser
values ('user1','Bill','Luther'),
('user2','Charlie','Martin'),
('user3','Dean','John')
;with cte_CountOfCreatedBy
as
(
select CreatedBY, COUNT(*) as CountCreatedBY
from @TableWO
group by CreatedBY
)
, cte_CountOfUpdatedBy
as
(
select UpdatedBy, COUNT(*) as CountUpdatedBy
from @TableWO
group by UpdatedBy
)
, cte_CountOfEventData
as
( select UpdatedBy
,SUM(CASE WHEN isnull(DataType,0) = 1 THEN 1 ELSE 0 END) as CountEventUpdated
,SUM(CASE WHEN isnull(DataType,0) != 1 THEN 1 ELSE 0 END) as CountDataUpdated
from @TableData
group by UpdatedBy
)
select u.UserId
,u.FirstName + ' ' + u.LastName as FullName
,ISNULL(cc.CountCreatedBY, 0) as CountCreatedBY
,ISNULL(cu.CountUpdatedBY, 0) as CountUpdatedBY
,ISNULL(ed.CountEventUpdated, 0) as CountEventUpdated
,ISNULL(ed.CountDataUpdated, 0) as CountDataUpdated
from @TableUser as u
left join cte_CountOfCreatedBy as cc
on cc.CreatedBY = u.UserId
left join cte_CountOfUpdatedBy as cu
on cu.UpdatedBY = u.UserId
left join cte_CountOfEventData as ed
on ed.UpdatedBy = u.UserId
It also worth to mention why the solution by Mark-101232 will not pruduce right counts.
As soon as it can find more than one row per user in any of the tables he LEFT JOINs to, it mulitplies number of row occurances (eg. for all other LEFT JOIN's), you can see it easely by removing counts and grouping from his query:
SELECT u.UserID,
u.Firstname,
--COUNT(wcb.CreatedBY) AS CountCreatedBY,
--COUNT(wub.UpdatedBY) AS CountUpdatedBY,
--COUNT(CASE WHEN d.DataType=1 THEN d.DataType END) AS EventUpdated,
--COUNT(CASE WHEN d.DataType<>1 THEN d.DataType END) AS EventDataUpdated
wcb.CreatedBY,
wub.UpdatedBY,
d.DataType
FROM @TableUser u
LEFT OUTER JOIN @TableWO wcb ON wcb.CreatedBY=u.UserID
LEFT OUTER JOIN @TableWO wub ON wub.UpdatedBY=u.UserID
LEFT OUTER JOIN @TableData d ON d.UpdatedBy=u.UserID
--GROUP BY u.UserID,u.Firstname
You can see that User1 will be found 4 times in wcb.CreatedBY and in wcb.UpdatedBY, when it should be only 2 and 1 respectively.

Good catch!. If the OP had provided tables with primary keys, things would have been easier

declare @TableWO table (PK int not null primary key, CreatedBY varchar(20), UpdatedBY varchar(20))

insert @TableWO

values (1, 'user1','user2'),

(2, 'user1','user1'),

(3, 'user2','user3')

declare @TableData table (PK int not null primary key, UpdatedBy varchar(20), DataType tinyint)

insert @TableData

values (1,'user1',1),

(2,'user3',2),

(3,'user3',2),

(4,'user2',1),

(5,'user1',1)

declare @TableUser table (UserID varchar(20), Firstname varchar(50), Lastname varchar(50))

insert @TableUser

values ('user1','Bill','Luther'),

('user2','Charlie','Martin'),

('user3','Dean','John')

SELECT u.UserID,

u.Firstname,

u.LastName,

COUNT(DISTINCT wcb.PK) AS CountCreatedBY,

COUNT(DISTINCT wub.PK) AS CountUpdatedBY,

COUNT(DISTINCT CASE WHEN d.DataType=1 THEN d.PK END) AS EventUpdated,

COUNT(DISTINCT CASE WHEN d.DataType<>1 THEN d.PK END) AS EventDataUpdated

FROM @TableUser u

LEFT OUTER JOIN @TableWO wcb ON wcb.CreatedBY=u.UserID

LEFT OUTER JOIN @TableWO wub ON wub.UpdatedBY=u.UserID

LEFT OUTER JOIN @TableData d ON d.UpdatedBy=u.UserID

GROUP BY u.UserID,u.Firstname,u.LastName;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

Mark-101232 (3/2/2012)
...
Good catch!. If the OP had provided tables with primary keys, things would have been easier
...

Could be easier, but also it may not be the case, depends on real PK's

Also, after reviewing the query again (both my and yours), I've found that it's possible to get rid of the second table scan of TableWO:

;with cte_CountOfCUBy

as

(

select u.UserId

,u.FirstName + ' ' + u.LastName as FullName

,sum(case when w.CreatedBY = u.UserID then 1 else 0 end) CountCreatedBy

,sum(case when w.UpdatedBY = u.UserID then 1 else 0 end) CountUpdatedBy

from @TableUser as u

left join @TableWO as w

on w.CreatedBY = u.UserID or w.UpdatedBY = u.UserId

group by u.UserId, u.FirstName + ' ' + u.LastName

)

, cte_CountOfEventData

as

( select UpdatedBy

,SUM(CASE WHEN isnull(DataType,0) = 1 THEN 1 ELSE 0 END) as CountEventUpdated

,SUM(CASE WHEN isnull(DataType,0) != 1 THEN 1 ELSE 0 END) as CountDataUpdated

from @TableData

group by UpdatedBy

)

select ucc.UserId

,ucc.FullName

,ISNULL(ucc.CountCreatedBY, 0) as CountCreatedBY

,ISNULL(ucc.CountUpdatedBY, 0) as CountUpdatedBY

,ISNULL(ed.CountEventUpdated, 0) as CountEventUpdated

,ISNULL(ed.CountDataUpdated, 0) as CountDataUpdated

from cte_CountOfCUBy as ucc

left join cte_CountOfEventData as ed

on ed.UpdatedBy = ucc.UserId

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

bhavtoshsharma SSC Rookie Points: 27 More actions · Answer 7

thank you all for quick responses..... the solution is just i was looking for and i will try to be more detailed and clear in my queries in future....