SQL query: Outer joins

[chpicker]

I want to issue a query on 2 tables with a one-to-many relationship. The query looks like this:

SELECT a.manifest,count(b.status) as numtrips ;
	FROM shift AS a LEFT OUTER JOIN callsin AS b ;
		ON a.manifest=b.manifest ;
	WHERE a.vehicle=myVar ;
	GROUP BY a.manifest

This produces the output I want, namely one record for each record in the SHIFT table for a specified vehicle with a count of matching records in the CALLSIN table. It includes records with no matching CALLSIN records and has a count of 0 for those records.

Now, however, I want to count only records in CALLSIN that match a certain criteria. So, I modify the SELECT statement like this:

SELECT a.manifest,count(b.status) as numtrips ;
	FROM shift AS a LEFT OUTER JOIN callsin AS b ;
		ON a.manifest=b.manifest ;
	WHERE a.vehicle=myVar AND b.status='C' ;
	GROUP BY a.manifest

The only change is the "AND b.status='C'" in the WHERE clause. However, I no longer get any results that have 0 matching records in CALLSIN. It's as if I used an INNER JOIN instead of a LEFT OUTER JOIN. Is there any way around this?

 

[fluteplr]

Try this, be careful with the parens.

SELECT a.manifest,count(b.status) as numtrips ;
FROM shift AS a LEFT OUTER JOIN callsin AS b ;
ON a.manifest=b.manifest ;
WHERE a.vehicle=myVar AND;
(b.status='C' or isnull(b.status)) ;
GROUP BY a.manifest

 

[jmcswain]

FYI: Whenever you add a field reference to a where clause, SQL (in any language) will tack on "AND NOT NULL" to the end of it. It's just the nature of SQL.

fluteplr's reply of course is the correct fix...I just wanted to point out that it's a SQL thing, not FoxPro.

 

[itflash]

as you are using groups, try using the HAVING condition instead.


Help file txt:


HAVING FilterCondition

Specifies a filter condition which groups must meet to be included in the query results. HAVING should be used with GROUP BY. It can include as many filter conditions as you like, connected with the AND or OR operators. You can also use NOT to reverse the value of a logical expression.



Alternative
-----------
otherwise, break the query down to first get all the callsin records that are = to c into a cursor and use that one instead.




i think the problem is in using the count and group by, that is why i suggested the having statement.
it gets too complicated for sql to sort out and therefore does not work.



hope this helps

 

[chpicker]

Thanks for the suggestions, guys...but it didn't work. I added "or isnull(b.status)" to the query with the extra set of parentheses, but all that did was make the query take longer; the results are exactly the same. I end up with 164 records where there should be 174 (10 records with 0 trips).

I've got a solution that SEEMS to work...mostly. I combined the "isnull" suggestion with ITFlash's HAVING suggestion and came up with 171 records. Some of them have 0s for the numtrips field! Now I just need to figure out what is different about the 3 records it missed and I should be in good shape.

By the way, ITFlash, the idea of breaking the query into 2 parts would work if I could figure out a fast way to do it. I tried a simple query of:

select * from callsin where status='C' into cursor myTemp

and it took over 2 minutes. The problem is that this is the primary table for the company that contains 1 record per transaction. At present it has over 170,000 records and it increases by about 1,000 per day. About 85% have a status of "C". I wish I could figure out how to get FoxPro to do its trick of just using a filtered table instead of building a new one. My understanding was that if the query was fully optimizable and contained no calculated fields that it would do it. So, the query must not be optimizable for some reason. The table is indexed on just plain "status", and I can't think of anything else that I can do to have an optimizable query.

Anyway, we'll see what turns up with these 3 missing records.

 

[chpicker]

I figured it out!

The corrected query should be:

SELECT a.manifest,count(b.status) as numtrips ;
    FROM shift AS a LEFT OUTER JOIN callsin AS b ;
        ON a.manifest=b.manifest AND b.status='C';
    WHERE a.vehicle=myVar ;
    GROUP BY a.manifest

The change? I moved "AND b.status='C'" to the JOIN condition instead of the filter condition. It pulls up all the right records now!

Thanks for all the suggestions, guys. )