WHERE clause disabling a LEFT JOIN?

[RandV80]

I'm working on an SQL query for Access, and have come across an unusual problem. Basically, simplifying names to keep it more generic, the table I'm creating will look like this:

Name |A1Count|A2Count|A3Count
------------------------------
GroupA| 0 | 3 | 4 |
GroupB| 2 | 0 | 1 |
GroupC| 0 | 0 | 0 |
...
and so on. I don't know if I need to go into detail on the actual SQL, but basically I have a fairly generic query, which sums up the counts from a more complex sub query:

Select G.Name, SUM(sqA1Count),...
FROM Group as G, (subquery) AS sq...

The subquery uses a Union as the A1 comes from a seperate table than A2 and A3, and so that I don't leave out counts with all 0's, I use a LEFT JOIN operator in it.

Everything works fine when I'm doing the full table, but I'm going to want to implement a form so that the user has the option of filtering the table according to count=0 or count>0 for either of the three activities. So depending on what options are selected, at the end of the generic query (outside the sub), I will be adding stuff like:
"WHERE A1Count = 0"
or
WHERE A1Count > 0 AND A3Count > 0"
and so on.

My problem is, when I try the first option, it works, but it eliminates all >1's counts from A2 and A3. So the results are:
0-6-1
0-7-4
0-1-2...
If I try it >0, then it gets all A1's above 0, but does the opossite and eliminates A2's and A3's at >0. It does the same thing if I try it with A2 or A3 as the WHERE. Going by that logic, if I try the 2nd example, I just don't get any results. This completely defeats the purpose of why I had a left join.

So can anyone help me out? Should I post the query?

 

[LKBrwnDBA]

>>...user has the option of filtering the table according to count=0 or count>0 for either of the three activities

When you use the word "either" in a requirement, that means you need to use the "OR" operator in the where clause:

SELECT ...
WHERE (A1Count =0 OR A2Count=0 OR A3Count=0);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[RandV80]

I'd certainly hope it isn't a case of myself messing up some simple logic, that would be bad. But it's a bit more comlpicated than that.

Basically, the query by itself gives me several hundred rows of 'groups', of 8 combinations of counts, binary-wise 000(=0) to 111(>0). It actually is working now for any combination of =0 for somereason, but not >0. If say WHERE A1Count > 0, I only want it to operate on that column. So matching results of 100,101,110,111. All I'm getting is 100.

I tried WHERE A1>0 AND A2>=0 AND A3>=0 just to be safe, but no difference. And if I do say WHERE A1>0 AND A3>0, so looking for results 101 and 111, I just don't get anything.

 

[unclejimbob]

I don't know if I need to go into detail on the actual SQL,"
FWIW I'd be inclined to post the whole SQL statement if its not too big so that the syntax of all the operations mentioned in your post becomes clearer.

 

[r937]

suppose you have a LEFT OUTER JOIN

FROM x LEFT OUTER JOIN y ON x.foo = y.bar

what you get is all rows of x, with or without matching rows from y

the "without matching rows" part is important, because otherwise you might as well run an INNER JOIN

okay, so now you write a WHERE clause on some column in the right table

WHERE y.qux = 'fap'

what happens is that on those rows of x which had no matching row in y, the column values that would've come from y are all set to NULL in the joined row which is produced by the LEFT OUTER JOIN

so if you use that WHERE clause, then since NULL is not equal to anything, that row from x is filtered out

this is typically not the desired effect

is this what is happening inside your subquery?



r937.com | rudy.ca

 

[RandV80]

That does sound like whats happening. I've never done much with SQL before, but while it's easy enough to pick up as you go along, I'll overlook things like this. At first glance though putting in an INNER JOIN seems to have the same effect, but I'll keep looking at it from that angle. Anyways, this is the query:

SELECT DISTINCT G.Name, G.ID,
Sum(IIf(uA.[Projects Count]>0,uA.[Projects Count],0)) AS [Activity1 Count],
Sum(IIf(uA.[Speaks Count]>0,uA.[Speaks Count],0)) AS [Activity2 Count],
Sum(IIf(uA.[Tours Count]>0,uA.[Tours Count],0)) AS [Activity3 Count]
FROM GROUP AS G,
(
SELECT G.Name, G.ID,
SUM(iif(AA.GroupID=G.ID,1,0)) AS [Activity1 Count],
0 AS [Activity2 Count],
0 AS [Activity3 Count]
FROM Group AS G LEFT JOIN ActivityA AS AA ON G.ID = AA.GroupID
WHERE G.[Inactive?]=False
GROUP BY G.Name, G.ID

UNION ALL SELECT G.Name, G.ID,
0 AS [Activity1 Count],
SUM(iif( AB.ActivityType = "Activity2", 1 , 0)) AS [Activty2 Count],
SUM(iif( AB.ActivityType = "Activity3", 1 , 0)) AS [Activty3 Count]
FROM Group AS G LEFT OUTER JOIN ActivtyB AS AB ON G.ID = AB.CompanyID
WHERE G.[Inactive?]=False
GROUP BY G.Name, G.ID
) AS uA
WHERE G.ID=uA.ID

/* A# =0/>0's go here */

GROUP BY G.Name, G.ID
ORDER BY G.Name;

A2 and A3 actualy come from the same table, but that's just minor detail. I also need to keep it in a single query format like this, as I'll be operating according to dates as well within the subquery. The query itself will be built as a String in VB code.

 

[RandV80]

Ignore the typo's in there, I changed some of the words to make it moer generic, but missed a few in the first part.

 

[r937]

i think you have G unnecessarily in the outer query

try this:

SELECT Name, ID
     , Sum([Projects Count]) 
          AS [Activity1 Count]
     , Sum([Speaks Count])   
          AS [Activity2 Count]
     , Sum([Tours Count])  
          AS [Activity3 Count]
  FROM 
  ( SELECT G.Name, G.ID
         , count(G.ID) AS [Activity1 Count]
         , 0 AS [Activity2 Count]
         , 0 AS [Activity3 Count]
      FROM Group AS G 
    LEFT OUTER 
      JOIN ActivityA AS AA 
        ON G.ID = AA.GroupID
     WHERE G.[Inactive?]=False
    GROUP 
        BY G.Name, G.ID
   UNION ALL 
    SELECT G.Name, G.ID
         , 0 AS [Activity1 Count]
         , SUM(iif(AB.ActivityType 
                    = "Activity2",1,0))
         , SUM(iif(AB.ActivityType 
                    = "Activity3",1,0))
      FROM Group AS G 
    LEFT OUTER 
      JOIN ActivtyB AS AB 
        ON G.ID = AB.CompanyID
     WHERE G.[Inactive?]=False
    GROUP 
        BY G.Name, G.ID
  ) 
/* WHERE A# =0/>0's go here */
GROUP BY Name, ID
ORDER BY Name

r937.com | rudy.ca

 

[RandV80]

ah yes, thanks for pointing that out. Initially I had the LEFT OUTER JOIN in the outer query. So if anything I at least cleaned this up a lot and made it more effecient.

The problem is still there though, but I've finally nailed down the actual problem! The Union query by itself actually produces duplicate results. If say group 12's count was 6-5-1, then the actual results it produces would be:

Group12 | 6 | 0 | 0 |
Group12 | 0 | 5 | 1 |

By using DISTINCT and SUM in the outer query, I only see them as one. So of course, when I say WHERE A1 > 0, there's actually two results, 6-0-0 and 0-5-1, so of course it's not going to pick up A2 and A3.

Now the question is how to modify the union subquery so it's not doing this. In theory I want to do something like a "DISTINCT UNION ALL SELECT", but obviously you can't do that.

 

[r937]

actually, based on your last explanation, i think now you want HAVING instead of WHERE

SELECT Name, ID
     , Sum([Projects Count]) 
          AS [Activity1 Count]
     , Sum([Speaks Count])   
          AS [Activity2 Count]
     , Sum([Tours Count])  
          AS [Activity3 Count]
  FROM 
  ( [i]subquery[/i]
  ) 
GROUP BY Name, ID
[b]HAVING Sum([Projects Count]) > 0[/b]
ORDER BY Name

r937.com | rudy.ca

 

[RandV80]

Thanks! That did it, I didn't know about the HAVING statement.