chpicker
Programmer
- Apr 10, 2001
- 1,316
- 0
- 0
I want to issue a query on 2 tables with a one-to-many relationship. The query looks like this:
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:
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?
Code:
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
Now, however, I want to count only records in CALLSIN that match a certain criteria. So, I modify the SELECT statement like this:
Code:
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