Left join without duplicate fields

I have three tables for orders of toy cars/planes:

         cars                          planes
=========================     =======================
id  manufacturer  model       id  manufacturer  model
1   Toyota        Trueno      1   Boeing        747
2   Toyota        Corolla     2   Airbus        A300
3   Mazda         RX7 FC      3   Airbus        A330
4   Subaru        WRX
5   Nissan        Silvia
6   Nissan        Silvia
7   Mazda         RX7 FD

orders
======
id  car_id  plane_id
1     1       NULL
2     2       NULL
3     3       NULL
4     4       NULL
5    NULL      1
6    NULL      2
7     5       NULL
8    NULL      3
9     6       NULL
10    7       NULL

How can I write an Ecto query that returns the N most recent orders (in terms of order id) without duplicate CAR manufacturers? (Orders with duplicate plane manufacturers are allowed.)

E.g. in the above tables, there are two Nissan car orders and two Mazda car orders. So, regardless of N, the older Nissan car order (order id=7) and the older Mazda car order (order_id=3) shouldn’t appear in the results. To illustrate, when N=5, the returned results should be the orders with IDs 10,9,8,6,5. When N=10, only the eight orders 10,9,8,6,5,4,2,1 should be returned.

By the way, the numbers of car or plane manufacturers here are potentially unbounded. We do not assume any known list of manufacturers in compile time.

Is this an SQL or an Ecto question? The latter supports group by, so if you know the SQL you want it should be easy to convert, but I hate SQL so I won’t guess :smile:

Not sure exactly how you should model your data but cars and planes appear to have identical columns. Why not combine them into one table and differentiate them with a new field like type

Then in your orders table you can have a datetime column like order_date that would make it easier to query for the most recent car manufacturers. That seems more simple to me but not sure your data can be modeled in this way.

              toys
    ================================
    id  manufacturer  model     type
    1   Toyota        Trueno    Car
    2   Toyota        Corolla   Car
    3   Mazda         RX7 FC    Car
    4   Subaru        WRX       Car
    5   Nissan        Silvia    Car
    6   Nissan        Silvia    Car
    7   Mazda         RX7 FD    Car
    8   Boeing        747       Plane
    9   Airbus        A300      Plane
    10  Airbus        A300      Plane


            orders
======================================
id  toy_id  order_date
1     1     2019-02-15 22:25:46.996577
2     2     2019-02-16 17:23:14.996577
3     3     2019-02-16 22:41:45.996577
4     4     2019-02-19 19:39:56.996577
5     5     2019-02-19 23:58:17.996577
6     6     2019-02-20 17:31:48.996577
7     7     2019-02-20 18:33:49.996577
8     8     2019-02-20 19:43:49.996577
9     9     2019-02-21 13:43:49.996577
10   10     2019-02-21 15:50:49.996577


Thanks, but my example here is a simplified (and probably over-simplified) one. I don’t think it’s OK to merge the tables in the original problem.

1 Like

Well you’d just left_join and then group_by, basically join on both tables, do a group_by to group over the field cars.manufacturer while selecting everything else.

What query have you already written, what does it do, and what do you want it to do (in either sql or ecto)?

2 Likes