I need to create a query of records in one table along with a count of how often those records occur in 2 other tables and the difference between the counts.

10 Years
Discussion Span
Last Post by mgouge

I am looking for something like:

SELECT Detail.No, Count(Detail.No), Count(Billing.No)  FROM Detail, Billing WHERE Detail.No *= Billing.No GROUP BY Detail.No

But the data returned by both Count(Detail.No) and Count(Billing.No) the product of Count(Detail.No) times(X) Count(Billing.No). For example, assume that Count(Detail.No) for Detail.No=1 should be 19 and Count(Billing.No) should be 4, the returned record would be

1, 76, 76

instead of
1, 19, 4

The ultimate result will eventually be Count(Detail.No) MINUS Count(Billing.No)
1, 19, 4, 15
but I thought I would take it step by step


This query works:

SELECT Stock.lStockId, Model.Name, Count(*) FROM amPortfolio WHERE (lStockId > 0) AND (lPortfolioItemId NOT IN (SELECT lPortfolioItemId FROM amReservation)) GROUP BY Stock.lStockId, Model.Name
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.