1

I am trying to join these 3 tables by key organization_id but I had no success. What I'm trying to achieve: I know ipo and I want to get some values from name_entries and address_entries. First query gives me organization_id, which is used in second and third query. Is this even possible in 1 query?

My current solution is calling these queries separately and then joining them in my code.

SELECT * FROM "rpo"."organization_identifier_entries" WHERE ipo = '********' ORDER BY updated_at DESC LIMIT 1;
SELECT * FROM "rpo"."organization_address_entries" WHERE organization_id='########' ORDER BY updated_at DESC LIMIT 1;
SELECT * FROM "rpo"."organization_name_entries" WHERE organization_id='########'  ORDER BY effective_to DESC NULLS FIRST LIMIT 1;
  • Are any of these unique? like ipo or organization ID on any of these tables? – Evan Carroll Aug 31 '18 at 17:32
  • No, they aren't - that's why I have the ORDERs and LIMITs in place. – Ondrej Vrabel Aug 31 '18 at 17:33
  • 1
    what a horrible schema. You don't have any PRIMARY KEYS on any of these tables? – Evan Carroll Aug 31 '18 at 17:34
  • 1
    All of them have "id". You helped me with my first question - now I'm processing data from the dump. – Ondrej Vrabel Aug 31 '18 at 17:36
  • 1
    ah. that's you =) You should punch them in the face, with love. Can't be your fault if it's someone else's data. – Evan Carroll Aug 31 '18 at 17:36
1

Using LEFT JOIN LATERAL

I think what you want is something like,

SELECT *
FROM rpo.organization_identifier_entries
LEFT JOIN LATERAL (
  SELECT *
  FROM rpo.organization_address_entries
  ORDER BY updated_at DESC
  FETCH FIRST ROW ONLY
) AS oae
  USING (organization_id)
LEFT JOIN LATERAL (
  SELECT *
  FROM rpo.organization_name_entries
  ORDER BY effective_to DESC NULLS FIRST
  FETCH FIRST ROW ONLY
) AS one
  USING (organization_id)
WHERE ipo = '********'
FETCH FIRST ROW ONLY;

Oh, and btw, never double-quote things in PostgreSQL unless you want to enable a very stupid capability to make identifiers caps-sensitive. This is a bad idea, unless that third party is also doing it and they have capital letters in their identifiers.

  • Thanks for the answer, but it did not work - using this I was only able to fetch organization_id and ipo from 1st table, everything else is NULL and Sqlectron says: "Duplicated columns: id, effective_from, effective_to, created_at, updated_at. It may cause the result in the second column overwriting the first one. Use an alias to avoid it." I tried specifying columns - I got rid of the duplicates error, but every other column was NULL. – Ondrej Vrabel Aug 31 '18 at 17:51
  • If the columns are null that means there is not a single row for that organization_id – Evan Carroll Aug 31 '18 at 17:54
  • That's weird - when I select the rows manually, I am able to find it. – Ondrej Vrabel Aug 31 '18 at 18:02
  • On the rows returned from this query with null, are you looking at the same organizatoin_id or are you looking on the basis of the IPO? And are you looking at the first row return with that sorting criterea (ie, is it finding something which is mostly null) – Evan Carroll Aug 31 '18 at 18:03
  • I don't really understand the question - I just replaced ******* with real IPO. – Ondrej Vrabel Aug 31 '18 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.