SQL Syntax

[mgouge]

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.

[omrsafetyo]

What tables are you querying and what columns are you comparing?

[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

[debasisdas]

is your join proper ?

[mgouge]

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