I have a unique identifier "AB" in one table, "financials", that has values that looks like '123-456.' In Another table, "project_info" I have two columns "A" and "B" A has the first part of the dash and B has the second.
E.g. if financials has the "AB" of '123-456', project_info's "A" is 123 and "B" is 456.
My goal is to select information from the financials table where "AB" is equal to "A" from projects_info 'dash' "B" from projects_info.
An ideal SQL query in my head would look something like this:
Select * from financials where "AB" = (select "A" from project_info)-(select "B" from project_info)
I know that's no where near correct but I've tried doing something like this:
select * from financials where "AB" = concat_ws((select "A" from projects_info), '-', (select "B" from projects_info))
the error I get and expect is
more than one row returned by a subquery used as an expression
Is something like this possible in plain PostgreSQL code?
As an added piece of info, I want to ultimately call this query in Node using the package node-postgres.com.
What you're trying to do is a join with that particular condition:
SELECT *
FROM financials f
JOIN projects_info pi ON(f."AB" = concat_ws('-', pi."A", pi."B")
Also you probably want to use concat(pi."A", '-', pi."B") or just pi."A" || '-' || pi."B" instead of concat_ws.