Re: How to do this in PostgreSQL? - Mailing list pgsql-sql

"Robert Nosko" <robertn@eori.net.pl> writes:
> In ORACLE I can perform the following query:

> SELECT tableA.id, title, qty
> FROM tableA, (SELECT id, count(*) qty FROM tableB group by id) tableC
> WHERE tableA.id = tableC.id (+)

> but in PostgreSQL I get error message.

We don't currently have sub-SELECTs in FROM (maybe in 7.1 though).
I doubt we ever will support that nonstandard "(+)" syntax (that's
Oracle-speak for an outer join, no?).

But 7.0 does handle sub-SELECTs in the result list, so you could do

SELECT id, title,      (SELECT count(*) FROM tableB where tableB.id = tableA.id) AS qty
FROM tableA;

This will be slower than the other way, probably, but it works and is
at least as readable.
        regards, tom lane