SQL Server SELECT Where Value Equals Results of Another Select

Question

I want to create a SQL statement to select values from one table based on the values from another. I would like to know how to do so in SQL, but knowing in PostgreSQL would be nice as well.

EX:

TableA
ID | Label | Value
1    Test    A


TableB
ID | Name | Label
1    TestN  Test

I think the query would looks something like this:

SELECT Name FROM TableB WHERE Label = SELECT Label FROM TableA WHERE Value = 'A';

That one throws errors though. Thoughts?

Answer 1

You're close... try this:

SELECT Name FROM TableB WHERE Label IN ( SELECT Label FROM TableA WHERE Value = 'A' );

EDIT: Started to add the INNER JOIN option but it is listed in another answer below.

Answer 2

Why don't you do a INNER JOIN?

SELECT DISTINCT B.Name
FROM TableB B
INNER JOIN TableA A ON B.Label = A.Label
WHERE A.Value = 'A'

Answer 3

use IN not equals:

select Name
from TableB
where Label in ( SELECT Label FROM TableA WHERE Value = 'A' );