Page 1 of 1

mysql - postgres query difference

Posted: Fri Jul 18, 2003 5:20 am
by jollyjumper
Hi all,

I'm trying to port my webapplication from mysql to postgres, and I've got a joined query that does work in mysql, but doesn't in postgres.

This is de query for mysql, I only want to know the id of the applications, and I want it only once per application id. This works perfectly in mysql.

Code: Select all

select * from nb_00_applicatie
left join nb_00_users_rechten on nb_00_applicatie.a_id = nb_00_users_rechten.ur_applicatieid
where nb_00_users_rechten.ur_userid = 1
group by nb_00_applicatie.a_id
When I execute this query on postgres, it gives a few errors about the group by, which says I need to add all of the other fields which are available in the tables nb_00_applicatie(application table) and 00_users_rechten(user rights table).

So after adding those I get this query:

Code: Select all

select * from nb_00_applicatie
left join nb_00_users_rechten on nb_00_applicatie.a_id = nb_00_users_rechten.ur_applicatieid
where nb_00_users_rechten.ur_userid = 1
group by nb_00_applicatie.a_id,
nb_00_applicatie.a_naam,
nb_00_applicatie.a_locatie,
nb_00_applicatie.a_omschrijving,
nb_00_users_rechten.ur_userid,
nb_00_users_rechten.ur_applicatieid,
nb_00_users_rechten.ur_schermid,
nb_00_users_rechten.ur_lezen,
nb_00_users_rechten.ur_toevoegen,
nb_00_users_rechten.ur_wijzigen,
nb_00_users_rechten.ur_verwijderen
But now it shows some application ids multiple times, because the users_rechten table contains multiple records about the rights the users has per application.

Could anyone tell me how to build a query with only a few group by fields in postgres ?

Thanks in advance.

Greetz Jolly.

Posted: Fri Jul 18, 2003 5:57 am
by jollyjumper
Sorry, found the sollution, just can't use the * for the fields, when I name the fields I want to have one by one it does work.

Greetz Jolly.