Solved

Tsql to Select Rows Based on a Condition

Posted on 2013-11-08
8
391 Views
Last Modified: 2013-11-08
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
Comment
Question by:Dovberman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 20

Assisted Solution

by:Daniel Van Der Werken
Daniel Van Der Werken earned 100 total points
ID: 39633618
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
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 100 total points
ID: 39633685
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
 

Author Comment

by:Dovberman
ID: 39633732
I will try both and give you feedback.

Thanks,
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 39633738
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
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 200 total points
ID: 39633829
The simplest query is:

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

Open in new window

0
 

Author Comment

by:Dovberman
ID: 39633848
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
 
LVL 27

Accepted Solution

by:
Zberteoc earned 200 total points
ID: 39633893
Use this:

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

Open in new window

0
 

Author Closing Comment

by:Dovberman
ID: 39633939
I wish to thank all of you.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question