Delete SQL Query - Brain Hurty

It's been a long day and I can't get this one right! Ugh!!! Should be
an EASY one...
Two tables:
Sales (IdProduct)
StoreListings (IdProduct)
I am trying to remove all items from our StoreListings database table
that have not had more than 15 sales, that is, they do not have more
than 15 records in the Sales table. I need a query that deletes from
StoreListings if the record count in Sales is < 15.
This is causing me great pain...delete StoreListing
where exists (select 1
from (select storeListingKey
from sales
group by storeListingKey
having count(*) < 15) as badSales
where storeListing.storeListingkey =
badSales.storeListingkey)
If this isn't right, post a script of your tables, just basic keys and
pertinent columns are all that is needed. Then a bit of data (we can change
it to 2 or 3 sales for developing the query.)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
<superfreaker@.gmail.com> wrote in message
news:1138151660.815868.236040@.g49g2000cwa.googlegroups.com...
> It's been a long day and I can't get this one right! Ugh!!! Should be
> an EASY one...
> Two tables:
> Sales (IdProduct)
> StoreListings (IdProduct)
> I am trying to remove all items from our StoreListings database table
> that have not had more than 15 sales, that is, they do not have more
> than 15 records in the Sales table. I need a query that deletes from
> StoreListings if the record count in Sales is < 15.
> This is causing me great pain...
>|||DELETE FROM StoreListing x
WHERE (SELECT COUNT(*)
FROM sales y
WHERE y.storeListingkey=x.storeListingkey)<15
"superfreaker@.gmail.com" wrote:

> It's been a long day and I can't get this one right! Ugh!!! Should be
> an EASY one...
> Two tables:
> Sales (IdProduct)
> StoreListings (IdProduct)
> I am trying to remove all items from our StoreListings database table
> that have not had more than 15 sales, that is, they do not have more
> than 15 records in the Sales table. I need a query that deletes from
> StoreListings if the record count in Sales is < 15.
> This is causing me great pain...
>|||DELETE FROM StoreListings
WHERE [IdProduct] IN
(SELECT t1.[IdProduct] FROM
(SELECT [IdProduct], COUNT(*) FROM Sales
GROUP BY [IdProduct]
HAVING COUNT(*) < 15
) t1
)
"superfreaker@.gmail.com" wrote:

> It's been a long day and I can't get this one right! Ugh!!! Should be
> an EASY one...
> Two tables:
> Sales (IdProduct)
> StoreListings (IdProduct)
> I am trying to remove all items from our StoreListings database table
> that have not had more than 15 sales, that is, they do not have more
> than 15 records in the Sales table. I need a query that deletes from
> StoreListings if the record count in Sales is < 15.
> This is causing me great pain...
>