Friday, August 8, 2008

[NOVICE] join group by etc

Hi List

I'm trying to convert some mysql queries to postgres and hitting a
brick wall with the following so was hoping for some hints.

table_one - iId,tId,toC,toD,toE
table_two - iId,fId,ttC,ttD
table_three - fId,tId,tthC,tthD,tthE,tthF

table_one data 11,9,o,1218177417,data
table_two data
11, 24, test1
11, 25, test2
11, 26, test4
11, 27, test6

table_three data
24,9,area1,t,y,3
25,9,area2,t,y,2
26,9,area3,a,y,1
27,9,area4,y,y,4

mysql query

SELECT T1.*, T2.ttC, T3.tthD, toD as sort FROM table_one T1 INNER JOIN
table_two T2 ON T1.iId = T2.iId INNER JOIN table_three T3 ON T3.fId =
T2.fId WHERE T1.tId = '9' and T1.toC = 'o' GROUP BY T1.iId ORDER BY sort
asc;

Which in mysql returns 1 row but fails in pg due to the group by.

If I add more fields to the group by or remove the group by it returns 4
rows which is incorrect

Basically I guess I am asking how I can get the same result in pg
without to much change in the sql.

Peter J

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

No comments: