I can't formulate my question, so I will try to explain here and hopefully change the title later on.

I have a table

Organisations (

Here is the tricky part: organisation.id represents a number for said organisation, but the same goes for the parent_id, it is basically another organisation. platform_id is either 1 or 2.

Now what I'm trying to do is to select all organisations where the parent_id is not null, but also where platform_id of the two organizations represented by organisation.id and organisation.parent_id is different.

How can I structure it, so that I can use the parent_id to search it as normal id, so that I can get its platform_id as well?

I will try to explain better and shorter perhaps:

I want to be able to filter those organisations, whose parent_id have different platform_ids, for that I would need to perhaps do a second select somehow...

sorry it got even more confusing.

This is the same table, parent_id is just another id that is linked to it, so that means that I can somehow access parent.id as normal id and get its platform_id...

In short, I want to list all IDs, that have parent_ids where the platform_id is different than the parents_id's platform_id, however I don't know how to get the 'parents_id'splatform_id`.

  • So I have select o.id, o.name, o.parent_id, o.platform_id from organization o where o.parent_id is not null and I also want, to somehow get the parent_id and search it a second time as if it is the o.id , so that I can also get its parent_id. – Ruthock Feb 13 at 13:31
  • 1
    the word you're looking for is "self-join" – mustaccio Feb 13 at 13:48
FROM Organisation t1
JOIN Organisation t2 ON t1.id = t2.parent_id
                    AND t1.platform_id != t2.platform_id;
|improve this answer|||||
  • Thank you! can you also guide me to where I can clarify a where t1.parent_id is not null – Ruthock Feb 13 at 13:52
  • @Ruthock Inner join with t1.id = t2.parent_id do not allow NULLs. – Akina Feb 13 at 13:54
  • This indeed selects the parent, can I somehow include in the select the ID of the child organisation? – Ruthock Feb 13 at 14:11
  • t2.id listed the desired child organisation! Many thanks guys, solved! – Ruthock Feb 13 at 14:12

As you have made abundantly and redundantly clear, you want to find all organizations where the organization referenced by parent_id has a different platform_id.

Translating that into SQL, you get a EXISTS condition:

FROM organisations o
              FROM organisations op
              WHERE o.parent_id = op.id
                AND o.platform_id <> op.platform_id);

You could also write it as a self join:

FROM organisations o
   JOIN organisations op
      ON o.parent_id = op.id
WHERE o.platform_id <> op.platform_id;

See what performs best.

|improve this answer|||||
  • Thank you, both work while the second version allows me to select information for the parents. – Ruthock Feb 13 at 14:23

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.