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.
What tables are you querying and what columns are you comparing?
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
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
is your join proper ?
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