i am new to sql server i am using stored procedures and trying to do multiple joins using same table and another join using first joined table for example i am trying get records from table A join with table B and C and table D joined using table B, but i am getting wrongs results if anyone can tell me what i am doing wrong
here is my query
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS itemNo,
ac.title,
ac.id,
ac.status,
count(comp.id) as total_comps,
count(comp_emps.id) as total_comp_emps,
count(assoc_emps.id) as total_assoc_emps,
--SUM(total_comp_emps,total_assoc_emps) total_comp_assoc_emps,
SUM((COALESCE(comp_emps.id,0)) (COALESCE(assoc_emps.id,0))) total_comp_assoc_emps,
--(total_comp_emps total_assoc_emps) as total_comp_assoc_emps,
count(u.id) as total_users
FROM associations ac
left join companies comp on comp.assoc_id = ac.id
left join users u on u.assoc_id = ac.id
left join employees comp_emps on comp_emps.comp_id = comp.id
left join employees assoc_emps on assoc_emps.assoc_id = ac.id
where ac.title like ('%' @search '%') and ac.status = @status
group by ac.title, ac.status, ac.id
order by ac.title asc
and getting this result
where total companies are 2 and users are 3 anyone please ?
CodePudding user response：
You are joining an association's users and companies. With 2 companies and 3 users you get six rows. Then you count count(comp.id) and count(u.id). Neither ID is null in the joined rows, so their results equal count(*) which is 6.
When dealing with multiple aggregations it is advisable to aggregate your data before joining. After all you want to join the number of companies and the number of users to your associations. These numbers are aggregation results.
As to employees this even gets a little more complicated, because employees can be related to an association either directly or by company. So with 5 employees linked by company and five employees linked by association, you may get, say, 8 total employees, in case two work for the company and are directly linked to the association, too. This may best be solved in a lateral outer join aka OUTER APPLY in SQL Server.
Well, coming from some few particular associations, you can just as well make all outer joins lateral.
select
row_number() over(order by (select 1)) as itemno,
a*, c.*, e.*, u.*
from associations a
outer apply
(
select count(*) as total_companies
from companies
where companies.assoc_id = a.id
) c
outer apply
(
select
count(*) as total_employees,
count(case when employees.assoc_id = a.id then 1 end) as association_employees,
count(case when employees.comp_id = c.id then 1 end) as company_employees
from employees
where employees.assoc_id = a.id
or employees.comp_id = c.id
) e
outer apply
(
select count(*) as total_users
from users
where users.assoc_id = a.id
) u
where a.title like ('%' @search '%') and a.status = @status
order by a.title;
This is much better than joining all single rows and then counting distinct IDs, because if you do that, you create a possibly huge intermediate result the DBMS must sort and look through to get the distinct counts. That can be very costly.
CodePudding user response：
When you start joining to tables down multiple paths of the relationship graph (such as associations -> companies -> employees, associations -> employees, and associations -> users) you start getting a lot of duplicate records that are next to impossible to sort out.
What I believe you need are some correlated subqueries that can independently inspect records down each path to calculate the results.
The following is one approach.
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS itemNo,
ac.title,
ac.id,
ac.status,
(
SELECT count(comp.id)
FROM companies comp
WHERE comp.assoc_id = ac.id
) as total_comps,
(
SELECT count(comp_emps.id)
FROM companies comp
JOIN employees comp_emps
ON comp_emps.comp_id = comp.id
WHERE comp.assoc_id = ac.id
) as total_comp_emps,
(
SELECT count(assoc_emps.id)
JOIN employees assoc_emps
WHERE assoc_emps.assoc_id = ac.id
) as total_assoc_emps,
--SUM(total_comp_emps,total_assoc_emps) total_comp_assoc_emps,
--SUM((COALESCE(comp_emps.id,0)) (COALESCE(assoc_emps.id,0))) total_comp_assoc_emps,
--(total_comp_emps total_assoc_emps) as total_comp_assoc_emps,
(
SELECT count(u.id)
FROM users u
WHERE u.assoc_id = ac.id
) as total_users
FROM associations ac
where ac.title like ('%' @search '%') and ac.status = @status
order by ac.title asc
You can also move intermediate calculations into a CROSS APPLY and then reference those intermediate results in your final select. This can be useful if you need to reuse certain results.
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS itemNo,
ac.title,
ac.id,
ac.status,
counts.total_comps,
counts.total_comp_emps,
counts.total_assoc_emps,
--SUM(total_comp_emps,total_assoc_emps) total_comp_assoc_emps,
--SUM((COALESCE(comp_emps.id,0)) (COALESCE(assoc_emps.id,0))) total_comp_assoc_emps,
(counts.total_comp_emps counts.total_assoc_emps) as total_comp_assoc_emps,
counts.total_users
FROM associations ac
CROSS APPLY (
SELECT
(
SELECT count(comp.id)
FROM companies comp
WHERE comp.assoc_id = ac.id
) as total_comps,
(
SELECT count(comp_emps.id)
FROM companies comp
JOIN employees comp_emps
ON comp_emps.comp_id = comp.id
WHERE comp.assoc_id = ac.id
) as total_comp_emps,
(
SELECT count(assoc_emps.id)
FROM employees assoc_emps
WHERE assoc_emps.assoc_id = ac.id
) as total_assoc_emps,
(
SELECT count(u.id)
FROM users u
WHERE u.assoc_id = ac.id
) as total_users
) counts
where ac.title like ('%' @search '%') and ac.status = @status
order by ac.title asc
I'm not clear on your intent for total_comp_assoc_emps, but the above is just add two intermediate values. If employees can belong to associations both directly and via companies, and you do not want to double count them, the total_comp_assoc_emps calculation is a bit more involved.
The following gathers employ IDs from both sources and counts each distinct ID once.
(
SELECT count(distinct id)
FROM (
SELECT comp_emps.id
FROM companies comp
JOIN employees comp_emps
ON comp_emps.comp_id = comp.id
WHERE comp.assoc_id = ac.id
UNION
SELECT assoc_emps.id
FROM employees assoc_emps
WHERE assoc_emps.assoc_id = ac.id
) emp_ids
) as total_assoc_emps,