Skip site navigation (1) Skip section navigation (2)

join group by etc

From: Peter Jackson <tasmaniac(at)iprimus(dot)com(dot)au>
To: pgsql <pgsql-novice(at)postgresql(dot)org>
Subject: join group by etc
Date: 2008-08-08 12:58:41
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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

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 

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


pgsql-novice by date

Next:From: Obe, ReginaDate: 2008-08-08 13:53:57
Subject: Re: join group by etc
Previous:From: A. KretschmerDate: 2008-08-08 07:55:06
Subject: Re: Calling an external command via triggers

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group