Re: join group by etc

Peter Jackson wrote:

> 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
>
You might try:
SELECT T1.*, T2.ttC, T3.tthD, toD AS sort DISTINCT ON (T1.iId) FROM ...

but I think you want to rethink what you're doing.  It looks like you 
want to select against one of the four matching entries in table_three- 
and I'm not sure which is the right one, or if just any will do.  With 
DISTINCT ON I don't think there is any gaurentee *which* of the four you 
will get- different environments might get different results.

Brian