CODEWITHSUNDEEP
sqlpostgresql
fLen
fLen

Reputation: 598

PostgreSQL: How to add Column from other table in select statement?

I'm trying to select a column from another table like I used to do in MSSQL:

 select * , Date = (select top 1 Date from [dbo].TableB where status = 1 order by Date desc)
 from [dbo].TableA

How can I do that in PostgreSQL?

Additional sample data:

TableA

Names

Richards
Marcos
Luke
Matthew
John

TableB

Date        Status
2016-01-01  1
2016-01-02  0
2016-01-03  1
2016-01-04  1
2016-01-05  1

Expected Output:

Name        Date
Richards    2016-01-02
Marcos      2016-01-02
Luke        2016-01-02
Matthew     2016-01-02
John        2016-01-02

Thanks!

Upvotes: 5

Views: 9413

Answers (5)

user330315
user330315

Reputation:

Date = (...) is invalid (standard) SQL and won't work in Postgres (or any other DBMS except for SQL Server)

A column alias is defined using AS ... in SQL (and Postgres). Postgres also doesn't have top. It uses limit.

Using square brackets in an identifier is also not allowed in SQL. So [dbo] needs to become dbo or "dbo" depending on how you created the schema.

select a.*, 
       (select date
        from dbo.tableb as b
        where b.status = 1 
        order by b.date desc
        limit 1) as date
from dbo.tablea a

date is a reserved word and should not be used as an identifier (column name)

If you want to use standard ANSI SQL, you can also use fetch first 1 row only instead of limit 1.

Another option would be to use max() instead of the limit in the sub-select which doesn't need the limit at all:

select a.*, 
       (select max(date)
        from dbo.tableb as b
        where b.status = 1) as date
from dbo.tablea a

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

You could try doing a CROSS JOIN:

SELECT * FROM
    (SELECT * FROM dbo.TableA),
    (SELECT Date FROM dbo.TableB WHERE status = 1 ORDER BY Date DESC LIMIT 1)

Upvotes: 0

Rodrigo Silver
Rodrigo Silver

Reputation: 5

Try this:

select a.*, b.column
from tableA as a, tableB as b;

Upvotes: 0

Licantropo
Licantropo

Reputation: 54

I'm not too familiar with PostgreSQL but SQL is still SQL. First thing to say is you have to have only one result on the second table query and you can do it in

 SELECT 
        A.*
        (select NewColumn from [dbo].TableB.NewColumn) as NewColumn 
FROM TableA

However I think you'll need to declare a join condition.

 SELECT 
        A.*
        (select NewColumn from [dbo].TableB.NewColumn where A.Col1 = TableB.col1)
FROM TableA A

without a real example I cant be more specific.

Upvotes: 0

Juan Pablo
Juan Pablo

Reputation: 1223

I'm not sure if this is the correct syntax, but did you try this:

select * , (select NewColumn from [dbo].TableB) as NewColumn 
 from [dbo].TableA

I hope it helps.

Upvotes: 1

Related Questions