Geek Answers Handbook

How to join two SQL queries in one?

I am new to SQL, and now I'm trying to learn how to create reports in Visual Studio. I need to make a table, graph and more. I decided to make the matrix last, and now I'm stuck. I am writing my queries in SQL Server.

I have two tables: Staff (empID, StaffLevel, Surname) and WorkOfArt (artID, name, curator, helpCurator). In the Curator and HelpingCurator columns, I used numbers from empID.

I would like my matrix to display each empID and the number of pictures in which they act as a curator, and the number of pictures in which they act as a curator of help (so I want three columns: empID, count (curator), count (helpCurator )

Select Staff.empID, count(WorkOfArt.Curator) as CuratorTotal
FROM Staff, WorkOfArt 
WHERE Staff.empID=WorkOfArt.Curator
and Staff.StaffLevel<7
group by Staff.empID;

Select Staff.empID, count(WorkOfArt.HelpingCurator) as HelpingCuratorTotal
FROM Staff, WorkOfArt 
WHERE Staff.empID=WorkOfArt.HelpingCurator
and Staff.StaffLevel<7
group by Staff.empID;

I created these two queries and they work fine, but I need this in a single query.

I tried:

Select Staff.empID, count(WorkOfArt.Curator) as CuratorTotal,
COUNT(WorkOfArt.HelpingCurator) as HelpingCuratorTotal
FROM Staff FULL OUTER JOIN WorkOfArt on Staff.empID=WorkOfArt.Curator
and Staff.empID=WorkOfArt.HelpingCurator
WHERE Staff.StaffLevel<7
group by Staff.empID;

( ) - empID, count 0s - :

Select Staff.empID, count(WorkOfArt.Curator) as CuratorTotal,
COUNT(WorkOfArt.HelpingCurator) as HelpingCuratorTotal
FROM Staff, WorkOfArt
WHERE Staff.empID=WorkOfArt.Curator
and Staff.empID=WorkOfArt.HelpingCurator
and Staff.StaffLevel<7
group by Staff.empID;

.

, . Google, , , , ... ? .

+4
3

, , select, - :

Select 
  S.empID, 
  (select count(*) from WorkOfArt C where C.Curator = S.empID) 
    as CuratorTotal,
  (select count(*) from WorkOfArt H where H.HelpingCurator = S.empID) 
    as HelpingCuratorTotal
FROM Staff S
WHERE S.StaffLevel<7
group by S.empID;

, . , WorkOfArt , .

+4

, ,

select e.EmpId, CuratorForCount, HelpingCuratorForCount 
  from Staff s
 inner join ( select Curator, count(*) as CuratorForCount
                from WorkOfArt
               group by Curator) mainCurator on s.EmpId = mainCurator.Curator
 inner join ( select HelpingCurator, count(*) as HelpingCuratorForCount
                from WorkOfArt
               group by HelpingCurator) secondaryCurator on s.EmpId = secondaryCurator.HelpingCurator
+1

One method that can be useful if you want to get more than one aggregated value from a table WorkOfArtis to pre-aggregate the results:

Select s.empID, COALESCE(woac.cnt, 0) as CuratorTotal, 
       COALESCE(woahc.cnt) as HelpingCuratorTotal
FROM Staff s LEFT JOIN
     (SELECT woa.Curator, COUNT(*) as cnt
      FROM WorkOfArt woa
      GROUP BY woa.Curator
     ) woac
     ON s.empID = woac.Curator LEFT JOIN
     (SELECT woa.HelpingCurator, COUNT(*) as cnt
      FROM WorkOfArt woa
      GROUP BY woa.HelpingCurator
     ) woahc
     ON s.empID = woahc.HelpingCurator
WHERE s.StaffLevel < 7;

Please note that external aggregation is not required.

+1
source

More articles:


All Articles