• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • Last Modified:

Tsql to Select Rows Based on a Condition

I have two tables.

I need to select rows in one table based on the number of times a common field occurs in the other table.

Table 1: Exceptions, Common field: SymbolID
Table 2: StockHist, Common field: SymbolID

SELECT SymbolID FROM Exceptions
WHERE Count(Exceptions.SymbolID) in StockHist is less than or equal to 5

Thanks,
0
Dovberman
Asked:
Dovberman
5 Solutions
 
Daniel Van Der WerkenIndependent ConsultantCommented:
Something like this:

SELECT EX.SymbolID
FROM Exceptions EX
INNER JOIN StockHist SH
   ON SH.SymbolID = EX.SymbolID
GROUP BY EX.SymbolID
HAVING COUNT(EX.SymbolID) <= 5

Open in new window

0
 
Brian CroweCommented:
You can alter the procedure below to use a correlated subquery instead of a common table expression but I prefer the latter for readability.

WITH cteStockHist (SymbolID, [Count])
AS
(
   SELECT SymbolID, COUNT(1) AS [Count]
   FROM StockHist
   GROUP BY SymbolID
   HAVING COUNT(1) >= 5
)
SELECT E.*
FROM Exceptions AS E
INNER JOIN cteStockHist AS cte
   ON E.SymbolID = cte.SymbolID
0
 
DovbermanAuthor Commented:
I will try both and give you feedback.

Thanks,
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Scott PletcherSenior DBACommented:
SELECT e.*
FROM Exceptions e
INNER JOIN (
    SELECT SymbolID, COUNT(*) AS Symbol_Count
    FROM StockHist
    --WHERE ...
    GROUP BY SymbolID
    HAVING COUNT(*) <= 5
) AS S ON
    s.SymbolID = e.SymbolID
--WHERE ...
0
 
ZberteocCommented:
The simplest query is:

SELECT 
	SymbolID 
FROM 
	Exceptions E
WHERE 
	(SELECT Count(*) FROM StockHist WHERE SymbolID=E.SymbolID)<=5

Open in new window

0
 
DovbermanAuthor Commented:
All three work.

Can the following be used to delete Exception table rows where the Exception.SymbolID occurs less than 5 times in the StockHist table? I do not want to delete StockHist rows.

DELETE FROM Exceptions EX
INNER JOIN StockHist SH
   ON SH.SymbolID = EX.SymbolID
GROUP BY EX.SymbolID
HAVING COUNT(EX.SymbolID) <= 5
0
 
ZberteocCommented:
Use this:

DELETE E FROM Exceptions E
WHERE 
	(SELECT Count(*) FROM StockHist WHERE SymbolID=E.SymbolID)<=5

Open in new window

0
 
DovbermanAuthor Commented:
I wish to thank all of you.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now