CODEWITHSUNDEEP
sql
Putt-Putt
Putt-Putt

Reputation: 21

How do I join two queries into one using table join

This is my first query using table join of two tables sharing the same primary key:

select s.Branch_ID, count(s.Branch_ID)  'No. of Staff', BranchName 'Branch Name'
from Sales_Staff s , Branch b
where s.Branch_ID = b.Branch_ID
group by s.Branch_ID, BranchName
order by 2 desc

Here is an image of how the output looks like

This is the second query:

select p.Branch_ID, BranchName, Count(p.Branch_ID) 'Frequency of Packages bought'
from Payment p, Branch b
where p.Branch_ID = b.Branch_ID
Group by p.Branch_ID, BranchName

Here's how the second one looks like

Now, the output for both was what I expected and wanted. However when I use the same method to join those two queries, the numbers are wrong.

select p.Branch_ID, BranchName, Count(p.Branch_ID) 'Frequency of Packages bought', count(s.Branch_ID) 'No. of Staff'
from Payment p, Sales_Staff s, Branch b
where p.Branch_ID = s.Branch_ID
and s.Branch_ID = b.Branch_ID
Group by b.Branch_ID, p.Branch_ID, BranchName

Incorrect Output Incorrect Output

Any help would be appreciated.

Upvotes: 0

Views: 66

Answers (1)

TomC
TomC

Reputation: 2814

Need to do the two group by's separately as you are getting many to many joins - every staff for every payment for each branch.

select  b.BranchID, b.BranchName, isnull(s.StaffCount,0) as StaffCount, isnull(p.PackageCount,0) as PackageCount
from    Branch b
left join (select Branch_ID, count(*) as StaffCount from SaleStaff group by Branch_ID) s on s.Branch_ID = b.Branch_ID
left join (select Branch_ID, count(*) as PackageCount from Payment group by Branch_ID) p on p.Branch_ID = b.Branch_ID

Upvotes: 2

Related Questions