Postgresql query involving three tables

Question

Hi there.

I have three tables, say:

(1) Name: Groups

Columns: gid, groupname

(2) Name: Profiles

Columns: profileid, profname

(3) Name: GroupsProfiles

Columns: gid, profileid - corresponds to the columns in the previous tables

Now, say I have an array of profileid's, I would like to construct a query which finds the corresponding gid in GroupsProfiles and then the corresponding groupname in Groups from that.

I have been playing about with joins and things but not quite getting what I want. Any ideas?

Answer 1

Use a join (replace your_profile_id with the profile ID you care about):

SELECT p.profileid
     , g.gid
     , g.GroupName
  FROM Groups g
     , Profiles p
     , GroupsProfiles gp
 WHERE g.gid = gp.gid
   AND p.profileid = gp.profileid
   AND p.profileid = your_profile_id

You would run this query for each profile ID. If you want to get the information for all profiles, or for a subset of profiles, you could remove the condition on your_profile_id, or you could also use an IN clause if you know which ones you want, e.g.

SELECT g.gid
     , g.GroupName
  FROM Groups g
     , Profiles p
     , GroupsProfiles gp
 WHERE g.gid = gp.gid
   AND p.profileid = gp.profileid
   AND p.profileid IN (profile_id_1, profile_id_2, ... ,profile_id_N)