Mysql – Finding rows in a table with a foreign key pointing back at itself where the referenced row differs in a certain column

MySQLpostgresql

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 (
   id,
   platform_id,
   parent_id
)

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`.

Best Answer

SELECT t1.*
FROM Organisation t1
JOIN Organisation t2 ON t1.id = t2.parent_id
                    AND t1.platform_id != t2.platform_id;
Related Question