How to use SQL select results in another sub select in same query / statement?-CodePudding

This is a little hard to explain - hopefully it is an easy solve. I have a table that I am doing a select on.

Here is the general query:

SELECT id, sub_id, name, inception_date, pc_code FROM investments;

This will pull everything which is great - but I need to use the sub_id field data that is returned from this select statement to re-query the table and pull in the name of the sub id. Here is what I am after in psudo code:

SELECT id, name, **sub_id**,
(SELECT name FROM investments where id = (sub_id from outer select statement)),
inception_date, pc_code
FROM investments;

sub_id and sub_id names would just query the same table for id and name.

I hope this makes sense and thanks for everyone's help!

CodePudding user response：

It looks like you don't need a subselect but double use of the same table with outer join, try this and be aware that the two coloumns from the table using sub_id can be null in case there is no match.

SELECT a.id, a.name, b.id , b.name, a.inception_date, a.pc_code
FROM investments a left outer join investments b on b.id = a.sub_id;

CodePudding user response：

Is this work?

WITH cte as (SELECT id,  name FROM investments )
SELECT a.id, a.name, a.sub_id,  a.inception_date, a.pc_code , b.name
FROM investments a
INNER JOIN cte b on b.id  = a.sub_id ;

Or easier

SELECT a.id, a.name, a.sub_id,  a.inception_date, a.pc_code , b.name
FROM investments a
INNER JOIN investments b on b.id  = a.sub_id ;