Search code examples
sqlrubypostgresqlsequel

How to select * from multiple tables in SQL when "self-joining"?

This query tries to get information about a company and its parent company:

select c.*, p.*
from companies c, companies p
where c.parent_id = p.id and c.name ilike '%google%'

but this seems to return data from the parent company (the one specified latter) only, and is missing the c.*.

Perhaps the reason is that because this is a self-join, the second one overrides the first ones?

I'm using this via the Sequal gem.

Solution

  • What you observe is not what Postgres does for this query. It returns all columns of the table companies twice, once for each instance, effectively duplicating column names, which can be a problem for some clients that would expect unique column names.

    db<>fiddle here