Solved

MS SQL n00b question on nested SELECT statement

Posted on 2009-04-14
3
347 Views
Last Modified: 2012-05-06
In the code section below you will find an SQL statement that works, but I want apply one extra "filter" to its result, perhaps by another SELECT statement?

When executing this query, I will get my results, but I want to filter those results out further by only getting records which match a particular test_name.

For example, test_name='aaa' .... how should I modify the query below? Thanks...
SELECT unique_id, test_name, test_object FROM tblSerializedTests
  WHERE unique_id IN 
   (SELECT unique_id FROM tblUserControls
    WHERE multiplicity='single'
      AND instrument='HP3245A'
      AND test_type='ACISource')

Open in new window

0
Comment
Question by:yaronusa
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24142020
this could do:
SELECT unique_id, test_name, test_object FROM tblSerializedTests
  WHERE unique_id IN 
   (SELECT unique_id FROM tblUserControls
    WHERE multiplicity='single'
      AND instrument='HP3245A'
      AND test_type='ACISource')
  AND test_name = 'aaa'

Open in new window

0
 

Author Comment

by:yaronusa
ID: 24142115
For a n00b, I was pretty close, but I put the 'And test_name = aaa' in the WRONG place.

Thanks for helping me... it is very appreciated.

SELECT unique_id, test_name, test_object FROM tblSerializedTests
  WHERE unique_id IN /*AND test_name = 'aaa' <--I'm wrong */
   (SELECT unique_id FROM tblUserControls
    WHERE multiplicity='single'
      AND instrument='HP3245A'
      AND test_type='ACISource')
  AND test_name = 'aaa' /* <--you're right */

Open in new window

0
 

Author Closing Comment

by:yaronusa
ID: 31570127
For a n00b, I was pretty close, but I put the 'And test_name = aaa' in the WRONG place.

Thanks for helping me... it is very appreciated.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

830 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