SQL: SELECT table and include field/value from another table

Question

what I would like to accomplish is selecting different values from one table and include a field value (which would be selected from another table) from another table.

Here is an example of my statement:

SELECT table1.Foo, <- ID
       table1.Bar, <- some value
       (SELECT table2.Baz from table2 <- another value I'd like to select from another table
        WHERE table1.Foo = table2.Foo and table2.Qux = 'A') As value1
FROM table1
INNER JOIN table2 ON table1.Foo = table2.Foo
WHERE table1.Bar = '2'

It is very important that value1 is a distinct value. The error that is being thrown is that the subquery (of value1) is containing more than one value.

I would kindly ask you for your help!

Edit: here is some sample data

table1

|---------------------|------------------|
|         Foo         |       Bar        |
|---------------------|------------------|
|          1          |        ABC       |
|---------------------|------------------|
|          2          |        XYZ       |
|---------------------|------------------|

table2

|---------------------|------------------|------------------|
|         Foo         |        Baz       |       Qux        |
|---------------------|------------------|------------------|
|          1          |        100       |        A         |
|---------------------|------------------|------------------|
|          1          |        200       |        B         |
|---------------------|------------------|------------------|
|          2          |        150       |        A         |
|---------------------|------------------|------------------|
|          2          |        175       |        B         |
|---------------------|------------------|------------------|

Result <- this is what I'd like to achieve

|---------------------|------------------|------------------|
|         Foo         |        Bar       |       value1     |
|---------------------|------------------|------------------|
|          1          |        ABC       |        100       |
|---------------------|------------------|------------------|
|          2          |        XYZ       |        150       |
|---------------------|------------------|------------------|

Greetings, Frame

Answer 1

You don't need to do JOIN again if you are using subquery:

select *,
       (select top 1 Baz from table2 where Foo = t1.Foo and Qux = 'A') as value1
from table1 t1;

Use LIMIT clause instead TOP in case doesn't support.

You can implement LIMIT clause as

select *,
        (select Baz 
         from table2 
         where Foo = t1.Foo and 
               Qux = 'A' 
         order by Baz -- Use DESC if necessary 
         LIMIT 1) as value1
from table1 t1;