CODEWITHSUNDEEP
mysql
Rpj
Rpj

Reputation: 6110

How do I sort based on a value from a 1-to-1 dependency

How do I sort foo's based on bar.val

create table foo (id, name)
create table bar (id, foo_id, val)

foo has 1-to-1 relationship with bar and some foo's will not have bar's

insert into foo (1, 'A');
insert into bar (1, 1, 'Active');

insert into foo (2, 'B');
insert into bar (2, 2, 'InActive');

insert into foo (3, 'C');
// 3 doesn't have a bar

insert into foo (4, 'D');
insert into bar (3, 4, 'InActive');

Upvotes: 1

Views: 23

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521944

I think this is what you are looking for:

SELECT f.id, f.name, b.val
FROM foo f LEFT JOIN bar b
ON f.id = b.foo_id
ORDER BY COALESCE(b.val, 'Archived') DESC

For those foo records which do not have a corresponding record in the bar table, they will be assigned a default value of "Archived" for the purpose of ordering the result set.

Upvotes: 2

Related Questions