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: 489C4301.6000406@iprimus.com.au (view raw or flat)
Thread:
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
  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

Responses

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-2014 The PostgreSQL Global Development Group