Left outer join using 2 of 3 tables in Postgresql

Question

I need to show all clients entered into the system for a date range. All clients are assigned to a group, but not necessarily to a staff.

When I run the query as such:

SELECT
 clients.name_lastfirst_cs, 
  to_char (clients.date_intake,'MM/DD/YY')AS Date_Created,
  clients.client_id, 
  clients.display_intake, 
  staff.staff_name_cs, 
  groups.name
FROM 
  public.clients, 
  public.groups, 
  public.staff, 
  public.link_group
WHERE 
  clients.zrud_staff = staff.zzud_staff AND
  clients.zzud_client = link_group.zrud_client AND
  groups.zzud_group = link_group.zrud_group AND
  clients.date_intake BETWEEN (now() - '8 days'::interval)::timestamp AND now()
ORDER BY
  groups.name ASC,
  clients.client_id ASC,    
  staff.staff_name_cs ASC

I get 121 entries

if I comment out:

SELECT
 clients.name_lastfirst_cs, 
  to_char (clients.date_intake,'MM/DD/YY')AS Date_Created,
  clients.client_id, 
  clients.display_intake, 
  -- staff.staff_name_cs,  -- Line Commented out
  groups.name
FROM 
  public.clients, 
  public.groups, 
  public.staff, 
  public.link_group
WHERE 
  --  clients.zrud_staff = staff.zzud_staff AND --Line commented out
  clients.zzud_client = link_group.zrud_client AND
  groups.zzud_group = link_group.zrud_group AND
  clients.date_intake BETWEEN (now() - '8 days'::interval)::timestamp AND now()
ORDER BY
  groups.name ASC,
  clients.client_id ASC,    
  staff.staff_name_cs ASC

I get 173 entries

I know I need to do an outer join to capture all clients regardless of if there is a staff assigned, but each attempt has failed. I have done outer joins with two tables, but adding a third has twisted my brain.

Thanks for any suggestions

Daniel B. Chapman · Accepted Answer · 2015-01-19 15:19:03Z

1

I have no way of testing this (or of knowing that it is right) but what I read in your query is that you want something similar to this:

SELECT --I just used short aliases. I choose something other than the table name so I know it is an alias "c" for client etc...
  c.name_lastfirst_cs, 
  to_char (c.date_intake,'MM/DD/YY')AS Date_Created,
  c.client_id, 
  c.display_intake, 
  s.staff_name_cs, 
  g.name,
  l.zrud_client AS "link_client",--I'm selecting some data here so that I can debug later, you can just filter this out with another select if you need to
  l.zzud_group AS "link_group" --Again, so I can see these relationships
FROM 
  public.clients c 
  LEFT OUTER JOIN staff s ON --is staff required? If it isn't then outer join (optional)
    s.zzud_staff = c.zrud_staff --so we linked staff to clients here
  LEFT OUTER JOIN public.link_group l ON --this looks like a lookup table to me so we select the lookup record
    l.zrud_client = c.zzud_client -- this is how I define the lookup, a client id
  LEFT OUTER JOIN public.groups g ON --then we use that to lookup a group
    g.zzup_group = l.zrud_group --which is defined by this data here
WHERE -- the following must be true
  c.date_intake BETWEEN (now() - '8 days'::interval)::timestamp AND now()

Now for the why: I've basically moved your where clause to JOIN x ON y=z syntax. In my experience this is a better way to write an maintain queries as it allows you to specify relationships between tables rather than doing a big-ol'-join and trying to filter that data with the where clause. Keep in mind each condition is REQUIRED not optional so when you say you want records with the following conditions you're going to get them (and if I read this right--I probably don't as I don't have a schema in-front of me) if a record is missing a link-table record OR a staff member you're going to filter it out.

Alternatively (possibly significantly slower) You can SELECT anything so you can chain it like:

SELECT 
  *
FROM
 (
   SELECT 
     *
   FROM 
     public.clients 
   WHERE
     x condition
  )
WHERE
  y condition

OR

SELECT * FROM x WHERE x.condition IN (SELECT * FROM y)

In your case this tactic probably won't be easier than a standard join syntax.

^And some serious opinion here: I recommend you use the join syntax I outlined above here. It is functionally the same as joining and specifying a where clause, but as you noted, if you don't understand the relationships it can cause a Cartesian join. http://www.tutorialspoint.com/sql/sql-cartesian-joins.htm . Lastly, I tend to specify what type of join I want. I write INNER JOIN and OUTER JOIN a lot in my queries because it helps the next person (usually me) figure out what the heck I meant. If it is optional use an outer join, if it is required use an inner join (default).

Good luck! There are much better SQL developers out there and there's probably another way to do it.

answered Jan 19, 2015 at 15:19

Daniel B. Chapman

4,68734 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Detox Over a year ago

Daniel, Thanks so much for the help! I really appreciate the explanations on each line. I did not need: l.zrud_client AS "link_client" OR l.zzud_group AS "link_group" as part of the end report, but I thought they had to be listed like that in order for the joins to work. If they cannot be "hid", I'll just delete them once the report is saved. I am re-reading each comment, and will be reading the link you provided. Again, thanks so much (I do have the schema on pdf for these 3 tables but did not know how to attach, or if it was possible to attach Again, Thank you

Daniel B. Chapman Over a year ago

You can filter them out with another select statement. Depending on backend optimizations if you don't touch a table sometimes the optimizer will screw up the joins. I had this problem with Advantage, not with Postgre but it put me in the habit of listing them. Just do -> SELECT x, y, z FROM (SELECT ... --full statment) this will "filter" what you need, you can also order at that point.

Collectives™ on Stack Overflow

Left outer join using 2 of 3 tables in Postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related