CODEWITHSUNDEEP
mysqlsqlsql-order-by
marius_neo
marius_neo

Reputation: 1595

SQL - ordering results by child attribute in a parent-child join

I'd like to know if there is a solution for the following situation.

I have a table SALE(id, client_name, total) and another table SALE_ITEM (id, sale_id, item_name, item_price). The relationship between SALE and SALE_ITEM tables is 1:n.

If I'd do a join between SALE and SALE_ITEM could i still (only via SQL) retrieve the results ordered by the item_name for each sale?

e.g.:

SALE
id          client_name                  total
1           John                          200
2           Aaron                         1400
3           Christian                     500

SALE_ITEM
id          sale_id               item_name                item_price
1           1                     Ruler                    100
2           1                     Crayon                   100 
3           2                     Bike                     1100
4           2                     Gloves                   300
5           3                     Jeans                    500

SALE JOIN SALE_ITEM (afer the ordering by sale_item#name field)
sale_id     sale_client_name     sale_total     sale_item_id    sale_item_name
2           Aaron                1400           3               Bike
2           Aaron                1400           4               Gloves
1           John                 200            2               Crayon
1           John                 200            1               Ruler
3           Christian            500            5               Jeans

As it can be observed from above, the results are ordered after the SALE_ITEM#item_name field (see Bike, ..., Crayon, ..., Jeans) , with the mention that the SALE_ITEM entries that belong to the same SALE are next to each other.

Upvotes: 1

Views: 321

Answers (3)

fthiella
fthiella

Reputation: 49079

This should give you the rows in the order that you need:

SELECT
  S.*, SI.*
FROM
  SALE S JOIN SALE_ITEM SI
  ON S.id = SI.sale_id JOIN
  (select SALE_ITEM.sale_id, min(item_name) as min_name
   from SALE_ITEM
   group by SALE_ITEM.sale_id) SI_MIN
  ON S.id=SI_MIN.sale_id
ORDER BY SI_MIN.min_name, SI.id

I'm sorting firts by the min_name for every sale, and then by SI.id. Please see this fiddle.

Upvotes: 1

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

SELECT * 
FROM SALE S
 JOIN SALE_ITEM SI
   ON S.id = SI.sale_id
 ORDER BY SI.sale_id, SI.item_name

Upvotes: 0

Avitus
Avitus

Reputation: 15968

select sale_id, client_Name, total, sale_item.id, item_name
from sale 
inner join sale_item on sale.id = sale_item.sale_id
order by sale.id, item_name

Upvotes: 0

Related Questions