how to get max rows from 2 transactional tables. MySQL

Question

I have two mysql transactional tables and and two lookup tables. I want to select max(id) from each of the transactional tables, combine the results with lookup tables and combine into one row. I seem unable to find solutions so far. Here is my tables. Stocks and Prices are transactional while Vehicle and Models are lookup tables.

Vehicles table

 id  name
 1   Toyota
 2   Suzuki

Models table

 id  vehicle_id  name
 1   1           Corolla
 2   2           Swift
 3   1           Prado
 4   2           Vitara

Stocks table

 id  vehicle_id  model_id  qty
 1   1           1         50
 2   2           2         77
 3   1           1         40
 4   2           2         30

Prices table

 id  vehicle_id  model_id  price
 1   1           1         500
 2   2           2         777
 3   1           1         600
 4   2           2         1000

Expected results

 id  vehicle_id  model_id  qty price  vname  mname
 1   1           1         40  600    Toyota Corolla
 2   2           2         30  1000   Suzuki Swift

Here is what I've tried among countless trials

select s.*, b.name vehicle, m.name model, p.price
                                  from stocks s, vehicles b, models m, prices p
                                  where s.id in (select max(id) id from stocks
                                  where s.vehicle_id = b.id and s.model_id = m.id  and s.vehicle_id = p.vehicle_id and s.model_id = p.model_id
                                  group by vehicle_id, model_id)
                                  order by id;

Running the above query doesn't give me what I want and it crushes the PC. I have to restart. How can I achieve the expected outcome?

Answer 1

If you are using MySQL 8 you can use window functions and common table expressions for latest(based on maximum id per vehicle and model group) prices and qty for vehicle and models

with pricescte as (select *,
  rank() over (partition by vehicle_id,model_id order by id desc) AS price_rank
from prices),
  stockcte as (select *,
    rank() over (partition by vehicle_id,model_id order by id desc) AS stock_rank
  from stocks)

select v.id,
  v.name,
  m.id as model_id,
  m.name,
  s.qty,
  p.price
from vehicles v
join models m on v.id = m.vehicle_id
join stockcte s on v.id = s.vehicle_id 
    and m.id = s.model_id
join pricescte p on v.id = p.vehicle_id 
    and m.id = p.model_id
where s.stock_rank = 1
    and p.price_rank = 1

DEMO

If you are not on latest version of MySQL < 8 you could use a query like

select v.id,
  v.name,
  m.id as model_id,
  m.name,
  s.qty,
  p.price
from vehicles v
join models m on v.id = m.vehicle_id
join (
    select *
    from stocks st
    where id = (
        select max(id)
        from stocks
        where st.vehicle_id =vehicle_id
          and st.model_id = model_id
    )
) s 
    on v.id = s.vehicle_id 
        and m.id = s.model_id
join (
    select *
    from prices pr
    where id = (
        select max(id)
        from prices
        where pr.vehicle_id =vehicle_id
          and pr.model_id = model_id
    )
) p on v.id = p.vehicle_id 
    and m.id = p.model_id

DEMO