PostgresQL - JOINs

[cciplkans]

This is regarding PostgresQl.
As per postgresQL Reference guide it is said that Joins(outer,inner,leftouter,rightouter) are
supported.
But when i run a query using the above mentioned joins ,i get a error message "not supported".

An example is given below.
I have two tables named TableA and TableB.

The fields of TableA are:
Aid(pk),Name.

The fields of TableB are:
Aid(fk),Detail.

say for example the data in TableA are
Aid Name
1 a
2 b
3 c

say for example the data in TableB are
Aid Detail
1 d1
3 d3
3 d33

the output which i need is

TableA.Aid count(TableB.Aid)
2 0
1 1
3 2

(ie) i need the count of records for each id in TableB which are present in TableA, in ascending order.
For the above scenario i used joins and i came to know those are not supported.
can anyone guide me regarding this.

[smarlowe]

Could you post your query and the results of a 'psql -V' please?

I use left, right, and inner joins all the time and they work great for me with 7.1.[1-3]

[Anon]

Here's a query that works on my 7.1.2 machine:

TABLEs:

t1:
a | b
-----+---
abc | 1
def | 2
ghi | 3

t2:
c | d
-----+---
aaa | 1
bbb | 1
ccc | 2

query:
select t1.b, count(t2.d) from t1 left join t2 on (t1.b=t2.d) group by t1.b;

Hope that helps.

[cciplkans]

we are using PostgreSQL server 7.1.3

we tried to execute your query. i didn't work in our server.

it gives the error message as follows

Failed to execute SQL : SQL select lawyers.lawyerid, count(casetrack.lawyerid) from lawyers left join casetrack on (lawyers.lawyerid=casetrack.lawyerid)group by lawyers.lawyerid failed : OUTER JOIN is not yet supported

please do needful.

regards
Kannan

[cciplkans]

we are using PostgreSQL server 7.1.3

we tried to execute your query. i didn't work in our server.

it gives the error message as follows

Failed to execute SQL : SQL select lawyers.lawyerid, count(casetrack.lawyerid) from lawyers left join casetrack on (lawyers.lawyerid=casetrack.lawyerid)group by lawyers.lawyerid failed : OUTER JOIN is not yet supported

please do needful.

[Anon]

OK, on the box that hosting the database, enter the following:

locate bin/postmaster

There should be only one. If there are two, try something like this:

path/to/bin/postmaster -V

for each one and see what you get.

[Anon]

Hey, I was wondering if you ever checked to make sure you were running 7.1 and not 7.0 on the server. I've tested inner / outer joins on 7.1 through 7.1.3 and they all supported it, so I'm guessing that the postmaster that is actually running is NOT 7.1 but an older one that was installed by default or earlier.

Well, if you still need help feel free to ask...