Need reult in one line
-
Hi All
I have these two queries.Now I need to combine the result of these two and get the resul in One line as
Agentname UpdateCount RefresherCount TEstCount
But how do i do it without using a temp table.How do I put both the queries in one so that I can get the desired result.
select D.LoginName,
sum(case when type='Update' then 1 else 0 end) as UpdateCount,
Sum(Case When type='Refresher' then 1 else 0 end) as RefresherCount
from UpdateApplicableTo as a, UpdateMaster as b, DepartmentMaster as c, LoginApplicableTo as e, LoginMaster as d
Where b.UpdateId = a.UpdateId
And a.ApplicableTo = c.DepartmentID
And e.LoginApplicableTo = a.ApplicableTo
And d.LoginID = e.LoginID and D.teamleader='TLALL' and D.Logintype='Agent'
And b.UpdateId NOT IN (Select UpdateId From UpdateReadBy)
Group BY D.LoginName
select D.Loginname,count(distinct t.updateid) as TestCount
From UpdateApplicableTo as a, UpdateMaster as b, DepartmentMaster as c, LoginApplicableTo as e, LoginMaster as d ,testmaster T
Where T.UpdateID=b.UpdateId and b.UpdateId = a.UpdateId
And a.ApplicableTo = c.DepartmentID
And e.LoginApplicableTo = a.ApplicableTo
And d.LoginID = e.LoginID and D.teamleader='TlAll' and D.logintype='Agent'
And t.UpdateId NOT IN (Select UpdateId From totalattempted)
Group BY D.LoginName
-
Without checking what your queries do and whether they couldn't be merged into one somehow.... you can do this (replace * with actual column names you want to display):
SELECT tblA.*, tblB.*
FROM
(select D.LoginName,
sum(case when type='Update' then 1 else 0 end) as UpdateCount,
Sum(Case When type='Refresher' then 1 else 0 end) as RefresherCount
from UpdateApplicableTo as a, UpdateMaster as b, DepartmentMaster as c, LoginApplicableTo as e, LoginMaster as d
Where b.UpdateId = a.UpdateId
And a.ApplicableTo = c.DepartmentID
And e.LoginApplicableTo = a.ApplicableTo
And d.LoginID = e.LoginID and D.teamleader='TLALL' and D.Logintype='Agent'
And b.UpdateId NOT IN (Select UpdateId From UpdateReadBy)
Group BY D.LoginName) as tblA
JOIN
(select D.Loginname,count(distinct t.updateid) as TestCount
From UpdateApplicableTo as a, UpdateMaster as b, DepartmentMaster as c, LoginApplicableTo as e, LoginMaster as d ,testmaster T
Where T.UpdateID=b.UpdateId and b.UpdateId = a.UpdateId
And a.ApplicableTo = c.DepartmentID
And e.LoginApplicableTo = a.ApplicableTo
And d.LoginID = e.LoginID and D.teamleader='TlAll' and D.logintype='Agent'
And t.UpdateId NOT IN (Select UpdateId From totalattempted)
Group BY D.LoginName) as tblB ON tblA.LoginName=tblB.LoginName
P.S.: Never tried to do this with a query written in the "old" syntax (table list+long WHERE instead of JOINing tables), but I guess it should work. JOIN syntax also allows you to avoid conditions like "NOT IN (select ....)", because this can be replaced with LEFT JOIN and IS NULL.
-
Hi
Thanks a lot it worked.But just one more thing.
You have written that " JOIN syntax also allows you to avoid conditions like "NOT IN (select ....)", because this can be replaced with LEFT JOIN and IS NULL."
How can I rewrite this query with the join conditions as you said.
-
Hi
Thanks a lot..Have written the query properly now.
SELECT TBLA.Loginname,TBLA.updatecount,TBLA.refreshercount,TBLB.testcount
from
(select E.LoginName,
sum(case when type='Update' then 1 else 0 end) as UpdateCount,
Sum(Case When type='Refresher' then 1 else 0 end) as RefresherCount
from UpdateApplicableTo A Inner Join UpdateMaster B ON A.UpdateID=B.UpdateID
Inner Join DepartmentMaster C ON A.ApplicableTo = C.DepartmentID
Inner Join LoginApplicableTo D ON A.ApplicableTo=D.LoginApplicableTo
Inner Join LoginMaster E ON D.LoginID = E.LoginID
Left Join UpdateReadBy F ON A.UpdateID=F.UpdateID
Where E.teamleader='TLALL' and E.Logintype='Agent' and F.updateid is NULL
Group BY E.LoginName) as tblA
JOIN
(select E.Loginname,count(distinct F.updateid) as TestCount
From UpdateApplicableTo A Inner Join UpdateMaster B ON A.UpdateID=B.UpdateID
Inner Join DepartmentMaster C ON A.ApplicableTo = C.DepartmentID
Inner Join LoginApplicableTo D ON A.ApplicableTo=D.LoginApplicableTo
Inner Join LoginMaster E ON D.LoginID = E.LoginID
Inner Join TestMaster F on B.UpdateId=F.UpdateID
Left Join totalattempted G ON F.UpdateID=G.UpdateID
Where E.teamleader='TLAll' and E.logintype='Agent'
And G.UpdateId Is NULL
Group BY E.LoginName) as tblB ON tblA.LoginName=tblB.LoginName
But if you could just explain a bit how does the left join and is null replace the not in.
-
Hi,
sorry, I was already away from work when you posted the question - but I see that you already found out how to use LEFT JOIN and IS NULL.
How this works? When you use OUTER JOIN (LEFT or RIGHT - I always use LEFT because it's easier to understand a query if you stick to one direction), it means, that (describing for FROM A LEFT JOIN B ON A.id=B.id):
a) the LEFT JOINed table B does not restrict the resultset; in absence of other conditions in WHERE, all rows from table A will be returned
b) if there are no rows in table B corresponding value in the joined column(s) - here B.id - any columns from this table will show NULL in the resultset
c) WHERE condition filters the resultset and is applied only in the next step, not at the same time with JOINs; at this moment, there are NULL values in the resultset (see b)) and so with "IS NULL" you remove all rows that don't have a matching row in table B.
I'm not sure what precisely happens in the DB, this is description which should just help you to understand how it works. Be careful - although you can theoretically evaluate any column from table B for NULLs in this scenario, if you happen to choose a column that really has NULL values in it, such row will be filtered out as well. The safest choice is to evaluate for NULLs the column on which you are joining (here - B.id).
If you want to find rows that don't have value 'test' in column B.infotext (both those that don't have a row in table B at all and those that have it, but not with value 'test'), you can do it this way:
SELECT a.*
FROM a
LEFT JOIN b ON b.id=a.id AND b.infotext = 'test'
WHERE b.id IS NULL
The reference to 'test' must be in the JOIN clause in order to work. And that is the difference between old and new syntax... it is not the same to place a condition into WHERE and into JOIN. (This is true only for OUTER JOINs though - I know of no difference between them when you are using INNER JOINs).
-
Thanks a Lot.hat made things really clear
-
Great explaination Vladan. Made me wonder if I should revisit some of my old joins to see if I did them correctly!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply