All we need is an easy explanation of the problem, so here it is.
I want to order children based on their parent’s last name. I am trying to use the following query:
SELECT * FROM children WHERE parent_id IN (SELECT parent_id FROM parent ORDER BY lastName ASC);
SELECT parent_id FROM parent ORDER BY lastName ASC returns the parent_ids in the order of the parent’s last names (this is the intended behavior). The ordering of parent_ids according to their last names in ascending order is
As you can see the results are ordered in ascending order of parent_id (not of the child’s respective parent’s last name).
Is there a way to correctly structure this query without changing my design?
How to solve :
The subselect returns a set, which has no order, the
order by there is useless – it will not apply to the final result set. If you want to order "children" by their "parent"s’ "last names", you’ll have to project those "last names" to the outer select by joining the two relations:
SELECT c.* FROM children c INNER JOIN parent p ON c.parent_id = p.parent_id ORDER BY lastName ASC
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂