mysql - postgres query difference

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jollyjumper
Forum Contributor
Posts: 107
Joined: Sat Jan 25, 2003 11:03 am

mysql - postgres query difference

Post 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.
jollyjumper
Forum Contributor
Posts: 107
Joined: Sat Jan 25, 2003 11:03 am

Post 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.
Post Reply