How to lookup value from a table and select it within the current table-CodePudding

I'm trying to lookup a value (c) from a table (Table2) and link it to a value (a) that I have in Table1. When I run the following lines I get an error "Unsupported subquery type cannot be evaluated". Not sure how to resolve it. Any help is much appreciated.

SELECT a, b, (SELECT c FROM Table2 AS T2 WHERE T1.a = T2.a) AS c, FROM Table1 AS T1;

I tried different subquery types but couldn't find the solution.

CodePudding user response：

you can join Table1 and Table2 on the column a so that you can select the value c from Table2 and link it to the value a in Table1.

Here's an example of how you can use a join statement to accomplish this:

SELECT T1.a, T1.b, T2.c
FROM Table1 T1
JOIN Table2 T2 ON T1.a = T2.a;

This query selects the columns a, b, and c from Table1 and Table2 respectively, and join them on column a of both tables.

Another way is to use left join if Table1 has more records than Table2.

SELECT T1.a, T1.b, T2.c
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.a = T2.a;

This will show all records from Table1 and the matching records from Table2. If Table2 doesn't have a matching record, the c column will be null.

Please note that the above examples are written in ANSI SQL standard and the syntax may vary depending on the specific database management system you are using.