mysql - postgres query difference
Posted: Fri Jul 18, 2003 5:20 am
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.
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:
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.
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_idSo 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_verwijderenCould anyone tell me how to build a query with only a few group by fields in postgres ?
Thanks in advance.
Greetz Jolly.