Join includes too many records in aggregate function

[Spork52]

Here's a simplified version of the problem:

There are four tables: order, user, group, user_group.

The order table has a user_id field indicating which user created the order, and a quantity field--order_qty.

A user can belong to multiple groups via an entry in the user_group table.

The group table has a field--group_type--which indicates whether group members are company employees or not.

How can a query be constructed which sums order_qty for orders made by employees.

Here's what I have so far, but it includes records more than once when a user belongs to more than one employee group:

SELECT field1, field2, SUM(order_qty)
FROM order
JOIN user_group
ON order.user_id = user_group.user_id
JOIN group
ON user_group.group_id = group.group_id
AND group.group_type = 'employee'
GROUP BY field1, field2

 

[Otto]

The right solution depends on where field1 and field2 come form. But you can write something like this:

SELECT field1, field2, SUM(order_qty) FROM order
 where exists (select group_type from group
  where group_type = 'employee' and
   group_id in (select group_id from user_group
    where order.user_id = user_id))
GROUP BY field1, field2

 

[Spork52]

Thank you for your answer. I was unaware of EXISTS and I think it might ultimately figure in the solution, but ... either I simplified the problem too much, or EXISTS is not behaving the way I expected (I thought it would only allow a single JOIN if the exists expression returns at least one row). Below is a more complete SELECT statement. I have incorporated EXIST into some of the LEFT JOIN conditions, but the they are still joining more than once.

The idea is to get a sum of order_qty for sales made or updated by employees and another sum for all other sales.

I am joining to the user_group table twice to pick up the user_id for the original sale and the update_user_id for any update.

The orgin expression evaluates to 1 or 0 depending on whether the sale is made or updated by an employee or not.

Group_ids 1, 3, and 11 are employee groups (I know this is a bad way to do things, but I'm stuck with it until I can modify the db tables).

Maybe I am going about this in a completely wrong and overly complicated way.

SELECT ISNULL(SUM(order.order_qty), 0) AS total_tickets,
ISNULL(a.group_id, ISNULL(b.group_id, 0))/ISNULL(a.group_id, ISNULL(b.group_id, -1)) AS orgin,
order.field1, order.field2

FROM order

LEFT JOIN user_group a
ON EXISTS
(
SELECT '1' FROM user_group
WHERE (group_id = 1 OR group_id = 3 OR group_id = 11)
AND a.user_id = order.user_id
)

LEFT JOIN user_group b
ON EXISTS
(
SELECT '1' FROM user_group
WHERE (group_id = 1 OR group_id = 3 OR group_id = 11)
AND b.user_id = order.update_user_id
)
AND ISNULL(a.group_id,0) <> 1
AND ISNULL(a.group_id,0) <> 3
AND ISNULL(a.group_id,0) <> 11

GROUP BY
ISNULL(a.group_id, ISNULL(b.group_id, 0))/ISNULL(a.group_id, ISNULL(b.group_id, -1)), order.field1, order.field2
ORDER BY order.field1, order.field2, orgin


I also tried:

LEFT JOIN user_group a
ON EXISTS (SELECT '1' FROM group WHERE (group_id = 1 OR group_id = 3 OR group_id = 11) AND a.group_id IN (SELECT group_id from user_group WHERE a.user_id = order.user_id) )

LEFT JOIN user_group b
ON EXISTS
(
SELECT '1' FROM group
WHERE (group_id = 1 OR group_id = 3 OR group_id = 11)
AND b.group_id
IN (SELECT group_id from user_group WHERE b.user_id = order.user_id)
)
AND ISNULL(a.group_id,0) <> 1
AND ISNULL(a.group_id,0) <> 3
AND ISNULL(a.group_id,0) <> 11

which I think is similar to what you suggested.

 

[SQLSister]

Think about using derived tables instead.

then you can use an aggregate function (and possibly group by if you need more than the join field) or use distinct to select one of the multiple records from the join table.

select a.field1, sum(t.field2) from table1 t
join
(select idfield, min(field1) from table2 group by idfield) a 
on t.idfield = a.idfield

"NOTHING is more important in a database than integrity." ESquared