CODEWITHSUNDEEP
sqlsql-server
Koralkea
Koralkea

Reputation: 43

How to join two SQL queries into one?

I'm new to SQL and I'm currently trying to learn how to make reports in Visual Studio. I need to make a table, graph and few other things. I decided to do matrix as the last part and now I'm stuck. I write my queries in SQL Server.

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

I'd like my matrix to show every empID and the number of paintings where they're acting as a Curator and the number of paintings where they're acting as a Helping Curator (so I want three columns: empID, count(curator), count(helpingCurator). 

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 those two queries and they work perfectly fine, but I need it in one 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;

(as well as using left or right outer join) - this one gives me a table with empID, but in both count columns there are only 0s - and:

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;

And this one gives me just the names of the columns.

I have no idea what to do next. I tried to find the answer in google, but all explanations I found were far more advanced for me, so I couldn't understand them... Could you please help me? Hints are fine as well.

Upvotes: 4

Views: 131

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

One method, that can be useful if you want to get more than one value aggregated value from the WorkOfArt table is 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;

Notice that the aggregation on the outer level is not needed.

Upvotes: 1

amcdermott
amcdermott

Reputation: 1585

From a performance perspective, the following query is probably a little more efficient

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

Upvotes: 1

James Z
James Z

Reputation: 12318

The easiest way to do this is most likely with inner select in the select clause, with something like this:

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;

This way the rows with different role aren't causing problems with the calculation. If the tables are really large or you have a lot of different roles, then most likely more complex query with grouping the items first in the WorkOfArt table might have better performance since this requires reading the rows twice.

Upvotes: 4

Related Questions