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?
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
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