CODEWITHSUNDEEP
sqlsqlite
oleole
oleole

Reputation: 207

Select Statement that orders depending on existence of foreign key in other table

I have two tables a and b where b contains a foreign key fk_a_id to table a id column.

Now I would like to select on table a but order the result depending on whether table b has a foreign key entry for it or not. the rows where table b does not have an entry for it should come first.

I haven't tried much yet, besides a join, which is probably not even the right direction.

select a.* 
from a as a
join b as b on b.fk_a_id = a.id
order by id desc

Upvotes: 1

Views: 107

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270723

One method is a left join. But that could duplicate rows if b contains multiple instances of a given key.

Another method uses logic in the order by:

select a.* 
from a
order by (case when not exists (select 1 from b where b.fk_a_id = a.id) then 1 else 2 end),
          id desc;

For performance, you would want an index on b(fk_a_id).

Upvotes: 2

Related Questions